Triggers for Audit Trail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Triggers for Audit Trail

Author

Topic

Leonardo
Starting Member

2 Posts

Posted - 2011-11-16 : 10:43:19

Hi, i am new to SQL and i have adopted the following method (http://www.codeproject.com/KB/database/AuditTrailGenerator.aspx#) for setting up a audit trail on my server. Front End Access 2007 / server MS 2008 Express.

This works fine:

CREATE TRIGGER tr_Cases_Insert ON dbo.Cases
FOR INSERT AS INSERT INTO Case_Audit(CaseID,Country,Region,AuditAction)
SELECT CaseID,Country,Region, 'I' FROM Inserted

However..i also need to create a trigger for updates ‘U’, and deletes, ‘D’...

Also..not sure if this is the right forum..when linking the audit table “Case_Audit” to my Access front end..i see all records as “deleted”..but the same table on the server displays correct values.

Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-16 : 10:49:08

Have a look at
http://www.nigelrivett.net/#Triggers

Use the generic table solution with care as it includes a lot of processing overhead.
Another option is to use cdc (I'm nearly complete with an article). It gives you a simple audit trail but loses a bit of information.
If you just want to hold the last 3 days of updates then the default and net changes sp is probably fine. Anything more you would need to process the cdc tables which is not a sinmple job - would go for triggers rather than that in the first instance.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Leonardo
Starting Member

2 Posts

Posted - 2011-11-16 : 11:11:15

Much appriciated Mr Rivett, i will read through this well and allow my self to come back with questions

Thanks!

james_wells
Yak Posting Veteran

55 Posts

Posted - 2011-11-16 : 13:22:35

CREATE TRIGGER tr_Cases_Insert ON dbo.Cases
FOR INSERT AS INSERT INTO Case_Audit(CaseID,Country,Region,AuditAction)
SELECT CaseID,Country,Region, 'I' FROM Inserted

i would use (for auditing)
after update
after delete
after insert

or maybe depending if you are using database replication or not

after update not for replication
after delete not for replication
after insert not for replication

There are two virtual tables
Inserted
deleted

update - creates an inserted and deleted record
insert - creates an inserted record
delete - creates a deleted record

Not sure if this is what you are after

Kristen
Test

22859 Posts

Posted - 2011-11-16 : 13:38:37

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215

Storing the INSERTED data is fine, but you are storing what is already in the table, so increasing your storage requirements.

We store what is DELETED (so nothing until the first UPDATE or DELETE) as that is less data to store, and we have the "latest version" in the actual table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources