Relational "history," ala, data-audit trail

I once had to hasitly implement a form of auditing and did what you are
suggesting (scripted the tables and added _audit to the names, created
triggers to select from inserted and deleted.

it worked, but it wasn't great. The tables grew quickly as every column
that changed was a whole new row in the audit. Every development change
had to update 2 tables and a trigger.

My advice would first be to identify if you really need to audit
everything. If so then I did once build up some very complicated
dynamic sql that would compare all the columns in a table and
dynamically update an audit table which was of the form tblAudit(table,
Column, ValBefore, ValAfter, DateChanged), however it's no mean task
and would require some serious dev time.

So in short, you can do better than what you suggest, it just depends
how much time you're willing to spend on it.

Cheers
Someone

roy.@nderson@gm@il.com wrote:
> This is unhelpful. I'm well aware of what triggers are.
>
> Another way to ask my question...should I create a duplicate of every
> table currently in existance and push all updates to those duplicate
> tables? How does one efficiently store historical changes? Think
> structure, not syntax.
>
>
>
> vt wrote:
> > BOL(Book Online).. read triggers..
> > vt
> >
> > "roy.@nderson@gm@il.com" wrote in message
> >
> > > Long story short: small company, 1 technical called upon to perform
> > > several roles. I've created a web-site allowing users to
> > > edit/insert/delete records. There are 8 tables involved here. The DB is
> > > relational. I have the task now of creating a site that allows admins
> > > to view the entire history of a record (all updates, edits, etc...).
> > >
> > > That's all nice and good but how do I accomplish this in a relational
> > > environment? The flatfile solution would be to create a table with all
> > > available fields and every time a user makes a change, push a new
> > > record into this gargantuan "archive table." I don't want to go down
> > > that route.
> > >
> > > Does anyone have any links to information on how to construct an
> > > efficient data audit/record history back-end structure? I'm sure this
> > > is a common requirement. I'm using SQL server 2k btw.
> > >
> > > Thanks
> > >
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

Use a third party tool that is driven off of the log files. Do not put
audit information in the same table or even in the same database. The
idea is that a single person should not have access to both the data
and the audit trail. You can go to jail under SOX and a few other laws.
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

BOL(Book Online).. read triggers..
vt

"roy.@nderson@gm@il.com" wrote in message

> Long story short: small company, 1 technical called upon to perform
> several roles. I've created a web-site allowing users to
> edit/insert/delete records. There are 8 tables involved here. The DB is
> relational. I have the task now of creating a site that allows admins
> to view the entire history of a record (all updates, edits, etc...).
>
> That's all nice and good but how do I accomplish this in a relational
> environment? The flatfile solution would be to create a table with all
> available fields and every time a user makes a change, push a new
> record into this gargantuan "archive table." I don't want to go down
> that route.
>
> Does anyone have any links to information on how to construct an
> efficient data audit/record history back-end structure? I'm sure this
> is a common requirement. I'm using SQL server 2k btw.
>
> Thanks
>
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

I hope this is at an acceptable technical level for you. Let me know if I
have to make it more complicated so as to not bruise your pride. As you
said, something similar is a fairly common requirement but the solution
depends on what you need to do with the data:

If you need to know who made what changes when for auditing type reasons
then tracing the DML commands would probably be the most efficient method.
The trace would go to a file and you would have to load the file into the
table periodically. This is the only easy way to get the who and when if
that is important,

If you need to know exactly which rows changed and what the change was,
triggers are a good solution but you already know all about that so I won't
discuss it.

Replication captures all changes from the log. You don't get a choice on
what format replication decides to give you the changes in but you can
create your own scripts for applying the changes and thus could put the
changes into a set of tables of your own design. This would probably be a
significant effort but would be pretty efficient.

As several people have said, if money isn't an issue there are several third
party tools that will troll the log for changes.

Bottom line, you need to start with the requirements for the final output
data and work back to find the best way to produce the required output.


--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"roy.@nderson@gm@il.com" wrote in message

> This is unhelpful. I'm well aware of what triggers are.
>
> Another way to ask my question...should I create a duplicate of every
> table currently in existance and push all updates to those duplicate
> tables? How does one efficiently store historical changes? Think
> structure, not syntax.
>
>
>
> vt wrote:
>> BOL(Book Online).. read triggers..
>> vt
>>
>> "roy.@nderson@gm@il.com" wrote in message
>>
>> > Long story short: small company, 1 technical called upon to perform
>> > several roles. I've created a web-site allowing users to
>> > edit/insert/delete records. There are 8 tables involved here. The DB is
>> > relational. I have the task now of creating a site that allows admins
>> > to view the entire history of a record (all updates, edits, etc...).
>> >
>> > That's all nice and good but how do I accomplish this in a relational
>> > environment? The flatfile solution would be to create a table with all
>> > available fields and every time a user makes a change, push a new
>> > record into this gargantuan "archive table." I don't want to go down
>> > that route.
>> >
>> > Does anyone have any links to information on how to construct an
>> > efficient data audit/record history back-end structure? I'm sure this
>> > is a common requirement. I'm using SQL server 2k btw.
>> >
>> > Thanks
>> >
>
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

See the following e.g

table a (main table)
==========

column1 column2
1 xxxx
2 yyyy

table (Audit table)
column1 column2 Action



On Update (let say updating xxxx to zzz)

table (Audit table)

column1 column2 Action
1 xxxx Update

table a (main table)

column1 column2
1 zzzz
2 yyyy




On delete (let say deleteing 1)

table (Audit table)

column1 column2 Action
1 xxxx Update
1 xxxx delete
table a (main table)

column1 column2
2 yyyy



Now to get history left join(Audit table and main table) these 2 table



is this what u want..


vt


"roy.@nderson@gm@il.com" wrote in message

> This is unhelpful. I'm well aware of what triggers are.
>
> Another way to ask my question...should I create a duplicate of every
> table currently in existance and push all updates to those duplicate
> tables? How does one efficiently store historical changes? Think
> structure, not syntax.
>
>
>
> vt wrote:
>> BOL(Book Online).. read triggers..
>> vt
>>
>> "roy.@nderson@gm@il.com" wrote in message
>>
>> > Long story short: small company, 1 technical called upon to perform
>> > several roles. I've created a web-site allowing users to
>> > edit/insert/delete records. There are 8 tables involved here. The DB is
>> > relational. I have the task now of creating a site that allows admins
>> > to view the entire history of a record (all updates, edits, etc...).
>> >
>> > That's all nice and good but how do I accomplish this in a relational
>> > environment? The flatfile solution would be to create a table with all
>> > available fields and every time a user makes a change, push a new
>> > record into this gargantuan "archive table." I don't want to go down
>> > that route.
>> >
>> > Does anyone have any links to information on how to construct an
>> > efficient data audit/record history back-end structure? I'm sure this
>> > is a common requirement. I'm using SQL server 2k btw.
>> >
>> > Thanks
>> >
>
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

Another option is to use any pf the 3:rd party products "out there" that extracts this information
from the transaction log. I would imagine that as a bonus, you would see less overhead...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"Someone Else" wrote in message

>I once had to hasitly implement a form of auditing and did what you are
> suggesting (scripted the tables and added _audit to the names, created
> triggers to select from inserted and deleted.
>
> it worked, but it wasn't great. The tables grew quickly as every column
> that changed was a whole new row in the audit. Every development change
> had to update 2 tables and a trigger.
>
> My advice would first be to identify if you really need to audit
> everything. If so then I did once build up some very complicated
> dynamic sql that would compare all the columns in a table and
> dynamically update an audit table which was of the form tblAudit(table,
> Column, ValBefore, ValAfter, DateChanged), however it's no mean task
> and would require some serious dev time.
>
> So in short, you can do better than what you suggest, it just depends
> how much time you're willing to spend on it.
>
> Cheers
> Someone
>
> roy.@nderson@gm@il.com wrote:
>> This is unhelpful. I'm well aware of what triggers are.
>>
>> Another way to ask my question...should I create a duplicate of every
>> table currently in existance and push all updates to those duplicate
>> tables? How does one efficiently store historical changes? Think
>> structure, not syntax.
>>
>>
>>
>> vt wrote:
>> > BOL(Book Online).. read triggers..
>> > vt
>> >
>> > "roy.@nderson@gm@il.com" wrote in message
>> >
>> > > Long story short: small company, 1 technical called upon to perform
>> > > several roles. I've created a web-site allowing users to
>> > > edit/insert/delete records. There are 8 tables involved here. The DB is
>> > > relational. I have the task now of creating a site that allows admins
>> > > to view the entire history of a record (all updates, edits, etc...).
>> > >
>> > > That's all nice and good but how do I accomplish this in a relational
>> > > environment? The flatfile solution would be to create a table with all
>> > > available fields and every time a user makes a change, push a new
>> > > record into this gargantuan "archive table." I don't want to go down
>> > > that route.
>> > >
>> > > Does anyone have any links to information on how to construct an
>> > > efficient data audit/record history back-end structure? I'm sure this
>> > > is a common requirement. I'm using SQL server 2k btw.
>> > >
>> > > Thanks
>> > >
>
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

So, given "sa" cannot have its access restricted, does that mean that
we need to audit out to a separate server?

indeed domain admins have access to the whole server too, so the
separate server would have to be on a separate domain, which would in
turn need to be untrusted to keep said domain admins out, which would
make writing to the audit log very hard for sql server, so such a
restriction appears to me to be unworkable.

I'm honestly not being facecous, I just don't understand how SOX can
expect such a condition to be enforced.

--CELKO-- wrote:
> Use a third party tool that is driven off of the log files. Do not put
> audit information in the same table or even in the same database. The
> idea is that a single person should not have access to both the data
> and the audit trail. You can go to jail under SOX and a few other laws.
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

>> So, given "sa" cannot have its access restricted, does that mean that we need to audit out to a separate server? <<

That could keep the Feds happy. SOX was supposed to be cleaned up this
year because it is too strict, but I am not sure if that will happen
with the changes in Congress. It is so bad that a number of companies,
such as Freescale Semiconductor here in Austin, dropped plans for an
IPO. As long as you are private, you do not have to file the reports.
I did a database review for a local software company that is marketing
a SOX compliance package that will help generate the required reports
and got to hear about what a nightmare it can be.

The basic principle is that the audit trail cannot be changed by the
party responsible for the data. I have to have an order signed by one
department and shipped by another with paper work in between to
authorize it. If there is a missing check number because you used
IDENTITY and got a gap, I need a SOX report on what happened to that
check number, etc. If you put the audit data in the same row as the
data it is tracking, then a DELETE FROM will destroy it; likewise for
an UPDATE.

This is pretty much the reason that you do not put the log on the same
hard drive as the database -- if the drive crashes, your back up dies
with the data

Many years ago, I did a volunteer job for a Food Co-op in Atlanta whcih
had gotten a mini-computer with SQL on it. The cowboy who wrote their
inventory program was so proud of the fact that "It's a relational
database! Anybody can change anything they want to!" The problem was
that everybody did change anything they wanted to. Physical inventory
never matched the database and when they went out of business, they had
5000 cases of Hot Sauce on the books and none on the shelf.

Your best bet is to talk to the accounting department and see what they
say.
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

--CELKO-- wrote:
> >> So, given "sa" cannot have its access restricted, does that mean that we need to audit out to a separate server? <<
>
> That could keep the Feds happy. SOX was supposed to be cleaned up this
> year because it is too strict, but I am not sure if that will happen
> with the changes in Congress. It is so bad that a number of companies,
> such as Freescale Semiconductor here in Austin, dropped plans for an
> IPO. As long as you are private, you do not have to file the reports.
> I did a database review for a local software company that is marketing
> a SOX compliance package that will help generate the required reports
> and got to hear about what a nightmare it can be.
>
> The basic principle is that the audit trail cannot be changed by the
> party responsible for the data. I have to have an order signed by one
> department and shipped by another with paper work in between to
> authorize it. If there is a missing check number because you used
> IDENTITY and got a gap, I need a SOX report on what happened to that
> check number, etc. If you put the audit data in the same row as the
> data it is tracking, then a DELETE FROM will destroy it; likewise for
> an UPDATE.
>
> This is pretty much the reason that you do not put the log on the same
> hard drive as the database -- if the drive crashes, your back up dies
> with the data
>
> Many years ago, I did a volunteer job for a Food Co-op in Atlanta whcih
> had gotten a mini-computer with SQL on it. The cowboy who wrote their
> inventory program was so proud of the fact that "It's a relational
> database! Anybody can change anything they want to!" The problem was
> that everybody did change anything they wanted to. Physical inventory
> never matched the database and when they went out of business, they had
> 5000 cases of Hot Sauce on the books and none on the shelf.
>
> Your best bet is to talk to the accounting department and see what they
> say.

1. Why are you assuming that SOX applies for the OP's database? In
fact, SOX has a very limited scope.
2. Even assuming that SOX applies, if there is an outside audit trail,
apparently it is acceptable to have audit columns like ModifiedBy,
ModifiedAt in the table itself. I think SOX mandates that there is a
separate audit trail, but it does not prohibit from maintaining other
audit trails, in the same table, or in the same database, or whatever.
Please correct me if I am wrong.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

If you are worried about SOX then I would strongly advise you to read up on
it rather than take somebodies interpretation (and celko's is wrong) on what
is required.

There is a wealth of information and products available - google "sql
server" sox

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


"Someone Else" wrote in message

> So, given "sa" cannot have its access restricted, does that mean that
> we need to audit out to a separate server?
>
> indeed domain admins have access to the whole server too, so the
> separate server would have to be on a separate domain, which would in
> turn need to be untrusted to keep said domain admins out, which would
> make writing to the audit log very hard for sql server, so such a
> restriction appears to me to be unworkable.
>
> I'm honestly not being facecous, I just don't understand how SOX can
> expect such a condition to be enforced.
>
> --CELKO-- wrote:
>> Use a third party tool that is driven off of the log files. Do not put
>> audit information in the same table or even in the same database. The
>> idea is that a single person should not have access to both the data
>> and the audit trail. You can go to jail under SOX and a few other laws.
>
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

> and when they went out of business, they had
> 5000 cases of Hot Sauce on the books and none on the shelf.

Too bad it wasn't the other way around. Would be heaven to me...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

--CELKO-- wrote:
> Use a third party tool that is driven off of the log files. Do not put
> audit information in the same table or even in the same database. The
> idea is that a single person should not have access to both the data
> and the audit trail. You can go to jail under SOX and a few other laws.

Would you mind citing the exact statute that would send him to jail
specifically for putting audit information in the same database as his
data? I've seen you make this claim on several occasions.
 >> Stay informed about: Relational ""history,"" ala, data-audit trail 

"Shuurai" wrote in message

>
> --CELKO-- wrote:
>> Use a third party tool that is driven off of the log files. Do not put
>> audit information in the same table or even in the same database. The
>> idea is that a single person should not have access to both the data
>> and the audit trail. You can go to jail under SOX and a few other laws.
>
> Would you mind citing the exact statute that would send him to jail
> specifically for putting audit information in the same database as his
> data? I've seen you make this claim on several occasions.

LOL. SOX is so freakin' generic there's no telling what they'll "throw you
in jail for." OTOH, I've seen flagrant violations of other regs (like FACTA
and HIPAA) that literally could result in some big fines (though probably
not jail time). I think Celko's main point is valid though - the audit data
should be stored separately.
 >> Stay informed about: Relational ""history,"" ala, data-audit trail