Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Auditing Insert Update and Delete Operations Expand / Collapse
Posted Thursday, April 23, 2009 2:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2015 10:00 PM
Points: 2, Visits: 85

I would like to put in place a process into the current oltp databases to facilitate the retrieval of 1) new records inserted; 2) updated records; 3) deleted records (if any).

I have an idea of using triggers to insert into an “audit” table the new, updated, deleted records with the appropriate timestamps on each. There is a caveat to this approach: I am not 100% certain what the previous developers used for retrieving identity. If they have not used the proper method ( i.e. @@Identity ), this can prove problematic to data integrity (e.g. wrong id’s used in a fk reference) especially if the audit tables have their own auto identity.

I would like to know is there any other solution apart from using triggers to implement this so analyze the and propose a strategy to implement a solution.
Post #702951
Posted Thursday, April 23, 2009 7:14 AM



Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 10,808, Visits: 14,832
I have always used audit tables and triggers for this type of auditing. Yes, you could cause a problem if the application code is using @@Identity to return the latest identity value if you put an identity column in your audit table. If you don't know what was used I would suggest not using an identity value on the audit table. My audit table would add these columns to the columns in that source table:

audit_action char(1) -- values would be I, U, D for insert, update, delete
audit_date smalldatetime

Then your unique/primary key would be source_id, audit_date, audit_action. Odds are the only joining you would do on the table will be on the source id you'd use that index for any join operations. You could cluster on audit_date, but I would think that a heap would be okay in this instance.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #703132
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse