Audit Trails/Transactions Log

[hvisionexpo]

I need to implement an audit trail or transaction log of transactions performed by the user. It needs to keep track of original value and new value and user/date-time.
I have about 10 tables on which I need to put this audit trail?


What is the best way to implement this?

Thanks

 

[Moptop]

Hello
You could use triggers for this. Although most people frown on the use of triggers, I think that this is a perfectly reasonable use.
The trigger would just have to compare the inserted and deleted pseudo tables and write differences away to an audit log.

Please remember that any code that you have that makes use of the row count would need to be check and retested as the results could be different.
Mark

 

[hvisionexpo]

Thanks.

I would like to pursue Triggers to create audit trail. When you sau psuedo tables, you mean create exact replica of existing tables where it would store before and after values? Could you please provide an example?

Also want to know if there is a naming convention for Triggers?

 

[kunai]

I also use triggers to keep track of user actions. Works great.

Create a table with the same structure as the table you are logging but with a few extra fields (e.g. typeOfAction, user, datetime)

Something like:

CREATE TRIGGER triggerName ON dbo.tableName
AFTER DELETE
AS
declare @fld1 varchar(50);
declare @fld2 varchar(50);

select 
@fld1  = fld1, 
@fld2  = fld2, 
from deleted;

insert into tableNameAudit values ('Delete', system_user, getdate(), @fld1, @fld2)

Hope this helps.