September 10, 2002 at 2:12 pm
I would like to audit all insert, updates and deletes to a databases tables. My problem is this. I was initially going to create an audit table with columns (id, column_name, data_value, user_id). Obviously I was going to utilize triggers for this however, my problem is this. If I insert 1 record for every column in a table for every insert,update, and delete I will take a huge performance hit especially with my big tables.
My ultimate question is this. What is the best way to keep track of tables (data inside the table not the table properties) and not take a huge performance hit. I want to track the value of a record when inserted, what its value was before and after it was updated and what its value was when it was deleted. further i want to track the user making the changes etc.
Any help would be greatly appreciated.
Thanks
September 10, 2002 at 2:41 pm
Possible Log Explore by Lumigent might work for you. I have not used the tool, but I'm sure someone might know whether it can do what you are asking. Here is the vendors website for Log Explore.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 10, 2002 at 4:30 pm
If you use the log technique, how long will you keep the data? If you need it for any length of time I'd go with the triggers. Yes, there is some overhead, but if you need it you need it! Be sure to forecase additional disk space requirements too.
Andy
September 11, 2002 at 6:54 am
Andy, what would be the best way to utilize the triggers. I wanted to make a large table, say 75 colunmns and just shoot 1 line into the table using triggers. The only thing is I can't get away with doing:
insert into table1
select * from inserted.
I get the obvious error (amount of colums doensn't match). Is there a way around this? Do you have any suggestions.
Thanks in advance.
quote:
If you use the log technique, how long will you keep the data? If you need it for any length of time I'd go with the triggers. Yes, there is some overhead, but if you need it you need it! Be sure to forecase additional disk space requirements too.Andy
http://www.sqlservercentral.com/columnists/awarren/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply