How to set up an audit trail in SQL 2005
I have a large database with a very key table (Tbl_General) that lots of people can update. I need to record all changes done by whom, and when. I do NOT need to worry about Inserts because they are very carefully controlled, and no DELETEs are allowed.
Is there a setting in SQL 2005 I can use to do this for me?
If not, I was thinking I could add 2 fields to the table (Last_User doing a change, and Date_Time_Changed), then make a copy of the table structure and call it Tbl_Audit_Trail. A trigger could then copy all of the fields for the record being updated to Tbl_Audit_Trail OR could copy only the fields being changed to Tbl_Audit_Trail.
What SQL statement would I use? Would this work?
INSERT into Tbl_Audit_Trail (*)
SELECT * FROM inserted
Is there a setting in SQL 2005 I can use to do this for me?
If not, I was thinking I could add 2 fields to the table (Last_User doing a change, and Date_Time_Changed), then make a copy of the table structure and call it Tbl_Audit_Trail. A trigger could then copy all of the fields for the record being updated to Tbl_Audit_Trail OR could copy only the fields being changed to Tbl_Audit_Trail.
What SQL statement would I use? Would this work?
INSERT into Tbl_Audit_Trail (*)
SELECT * FROM inserted
Some software to do it:
http://www.sqlpower.com/cgi-bin/eval_form.cgi?referrer=Go&gclid=CPn3scHp8I4CFQ2aOAodFU3FBw
http://www.apexsql.com/sql_tools_log.asp
The free products seem to work only for sql server 7
http://www.sqlpower.com/cgi-bin/eval_form.cgi?referrer=Go&gclid=CPn3scHp8I4CFQ2aOAodFU3FBw
http://www.apexsql.com/sql_tools_log.asp
The free products seem to work only for sql server 7
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
your moms "WEAK" dgmg
ASKER
dqmq -- your solution was exactly what I had been thinking of...
BUT
SQL_SERVER_DBA -- Your solution might be more useful...
SO
I am trying out both methods to see which I prefer.
In both cases, I am getting the following messages:
On select * into #ins from inserted
I get:
"Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."
On select * into #del from deleted
I get:
"Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."
I have numerous Text and NText fields in the table I am trying to audit trail (but no image fields). What do I do.
BUT
SQL_SERVER_DBA -- Your solution might be more useful...
SO
I am trying out both methods to see which I prefer.
In both cases, I am getting the following messages:
On select * into #ins from inserted
I get:
"Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."
On select * into #del from deleted
I get:
"Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."
I have numerous Text and NText fields in the table I am trying to audit trail (but no image fields). What do I do.
Fields with those datatypes are not present in the inserted and deleted tables. You can leave them out of your audit, like using an explicit list of columns (instead of *) which omits the text columns:
select col1, col2, col2 into #ins from inserted
If you want them in the audit table, then it's more complicated. First, it would be difficult capture the "after image" for your audit, but you can capture the "before image", like this:
select D.*, system_user UpdateBy, getdate() UpdateTime into #ins
from YourTable T inner join Deleted D on D.PK = T.PK
select col1, col2, col2 into #ins from inserted
If you want them in the audit table, then it's more complicated. First, it would be difficult capture the "after image" for your audit, but you can capture the "before image", like this:
select D.*, system_user UpdateBy, getdate() UpdateTime into #ins
from YourTable T inner join Deleted D on D.PK = T.PK
ASKER
I used the suggestions provided by dqmq to develop one trigger (Scenario 1), and also adapted the solution provided by SQL_SERVER_DBA into another trigger (Scenario 2).
FYI -- Two things to know about this application:
A. Updates to the database can only be done using a front-end executable -- no one has the ability to go directly into the database, such as with Management Studio. The front-end allows Inserts and Updates, but does not allow any Deletes (instead, we just mark the records as "Out of Scope").
B. Since each record in the target table is assigned to a specific user, the likelihood of 2 people actually updating the same record simultaneously is so close to zero as to not be a concern.
Scenario 1 (using the dqmq solution): The concept of this scenario (and of my original idea) was to create Audit Trail records that contained the entire updated record from the target table. Later, if necessary, I would be able to select all records for a PK and see the progression of changes. The problem was that the target table included "text" fields.
I set up the trigger on the target table to select all columns by name from "inserted" (eliminating the 6 that were "text", which I understand were not in "inserted") and put the selected values into a new record in the Audit Trail table.
Then, I created a trigger on the Audit Trail table that, on insert, uses the primary key just inserted into the Audit Trail table to grab the now-updated values of those 6 text fields from the target table and update the recorded just inserted into the Audit Trail table. (Pretty slick, I thought!)
Scenario 2 (using the SQL_SERVER_DBA solution): The concept of this scenario was to create Audit Trail records that contained both the old and new values from only those fields being updated for a given record in the target table. Later, if necessary, I would be able to select all records for a PK and a given field to follow the trail of changes.
Because of the "text" fields, I had to also select fields from "inserted" by name. However, because this scenario records both the old and new values, while I knew I could grab the new values, I could figure out no way to grab the old values.
This prompted me to look into why we are using "text" fields instead of "varchar" fields. It turns out that the maximum length provided by varchars (8,000 chars) is much more than adequate. So, I changed the data type of the 6 fields to varchar. Then I went back in to the trigger and did "SELECT *" instead of selecting specific fields.
BOTTOM LINE: While I found Scenario 1 to be an acceptable solution (and the implementation could have been improved from the change of datatype I discovered while working on Scenario 2, thus eliminating the second trigger on the Audit Trail table), I chose Scenario 2 because it only collects the fields that actually change, versus Scenario 1 collecting every field. The net result is my database will grow more slowly.
Because both of you helped me, and prompted me to learn more than I would have otherwise, I am splitting the points, giving the larger share to SQL_SERVER_DBA for turning me on to a more economical solution.
FYI -- Two things to know about this application:
A. Updates to the database can only be done using a front-end executable -- no one has the ability to go directly into the database, such as with Management Studio. The front-end allows Inserts and Updates, but does not allow any Deletes (instead, we just mark the records as "Out of Scope").
B. Since each record in the target table is assigned to a specific user, the likelihood of 2 people actually updating the same record simultaneously is so close to zero as to not be a concern.
Scenario 1 (using the dqmq solution): The concept of this scenario (and of my original idea) was to create Audit Trail records that contained the entire updated record from the target table. Later, if necessary, I would be able to select all records for a PK and see the progression of changes. The problem was that the target table included "text" fields.
I set up the trigger on the target table to select all columns by name from "inserted" (eliminating the 6 that were "text", which I understand were not in "inserted") and put the selected values into a new record in the Audit Trail table.
Then, I created a trigger on the Audit Trail table that, on insert, uses the primary key just inserted into the Audit Trail table to grab the now-updated values of those 6 text fields from the target table and update the recorded just inserted into the Audit Trail table. (Pretty slick, I thought!)
Scenario 2 (using the SQL_SERVER_DBA solution): The concept of this scenario was to create Audit Trail records that contained both the old and new values from only those fields being updated for a given record in the target table. Later, if necessary, I would be able to select all records for a PK and a given field to follow the trail of changes.
Because of the "text" fields, I had to also select fields from "inserted" by name. However, because this scenario records both the old and new values, while I knew I could grab the new values, I could figure out no way to grab the old values.
This prompted me to look into why we are using "text" fields instead of "varchar" fields. It turns out that the maximum length provided by varchars (8,000 chars) is much more than adequate. So, I changed the data type of the 6 fields to varchar. Then I went back in to the trigger and did "SELECT *" instead of selecting specific fields.
BOTTOM LINE: While I found Scenario 1 to be an acceptable solution (and the implementation could have been improved from the change of datatype I discovered while working on Scenario 2, thus eliminating the second trigger on the Audit Trail table), I chose Scenario 2 because it only collects the fields that actually change, versus Scenario 1 collecting every field. The net result is my database will grow more slowly.
Because both of you helped me, and prompted me to learn more than I would have otherwise, I am splitting the points, giving the larger share to SQL_SERVER_DBA for turning me on to a more economical solution.
Thanks for the followup. It's always nice to see how our contributions played out.
dQm
dQm
ASKER
dqmq - I always try to do that, but sometimes I fail or forget. When that happens, it is frequently because I am working 29-hour days, 7 days a week!
ASKER
I need to ask an URGENT follow-up question...
Since I had to do this:
select col1, col2, col2 into #ins from inserted
Rather than this:
select * into #ins from inserted
Does this mean when I attempt to update the table, I have to always include all of those columns in the update command?
Since I had to do this:
select col1, col2, col2 into #ins from inserted
Rather than this:
select * into #ins from inserted
Does this mean when I attempt to update the table, I have to always include all of those columns in the update command?
ASKER
Hit the Submit button too soon....
If that is true, then how can I build a single trigger that will handle multiple types of updates, each with different columns involved?
If that is true, then how can I build a single trigger that will handle multiple types of updates, each with different columns involved?
http://www.aspfree.com/c/a/Database/Building-an-Audit-Trail/