November 6, 2009 at 6:57 am
Dear All,
Our application uses SQL Server 2000.
I want to maintain audit trail information in our database for many tables.
Our application has 100s of users, so per day approx overall 1000s of insert and update statements are executed.
If I use triggers then it will slow down our system as database size and number of transactions are huge.
Can any one suggest best way to achieve this feature?
Are there any alternatives to triggers?
Thanks,
Nikesh Shah
November 6, 2009 at 7:09 am
I perfer to use triggers but a couple of suggestions;
You could modify your insert procedures to log all the inserts to an audit table after they have written to the main data table,
or you could use a log-reader to read the transaction log when you need to audit changes, this would require no development work and I think the log-reader for SQL2000 from redgate is free..
November 6, 2009 at 7:19 am
It mainly depends on what you want to accomplish with your audit log.
You could use a transaction log parser. Database needs to be in full recovery (should be anyway on an OLTP database). Advantages are no additional overhead. Disadvantages are that you have to buy a reader, and they are more than a little slow.
You could use a server-side trace. Microscopic overhead if you have it write to a text file. Main disadvantage is that it doesn't give you the data, it gives you the DML command that was issued. So you don't know what rows were affected without further querying. But it's very thorough and has very little impact on the server. You will need to manage the trace files, to make sure they don't grow too much.
A well-written trigger-based solution will add a few milliseconds per transaction. At thousands of transactions per day, that should be no problem. A poorly-written trigger solution would be a problem at that volume, but a well-written one is okay.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2009 at 1:47 am
Thanks for the response.
I have one more question here.
If I create trigger on update of some column, does it lock that row till the trigger finishes updation of the audit trail table?
November 9, 2009 at 6:53 am
porwal.naveen (11/9/2009)
Thanks for the response.I have one more question here.
If I create trigger on update of some column, does it lock that row till the trigger finishes updation of the audit trail table?
Yes. Triggers are synchronous. The transaction won't complete till the trigger is done.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply