Auditing Trail with Trigger in SQL Server 2005

Thursday, March 26, 2009 |

If you are new to this concept in database world than you might think what is Auditing Trail? The answer is pretty much easy and simple, if you want to increase the data-integrity and wants to have full details about insertion, edition and deletion of your data; Audit Trail is the concept you have to adopt.

Since this is a very huge topic and not possible to cover complete topic in one or may be in few articles, I will give some basic details here and will write some more article with live situation as and when time permits.

You would like to keep track of total update and delete in records after its insert, you may want which front-end accessed your row for modification, which user has changed which records.

There are many methods popular to keep track of your records change like you can have duplicate table for each table along with some comment field sand keep the track and old value. You may wish to create one table which will have details off all tables and about every single transaction but In that case, maintenance of that table is very crucial.

Let us create one table and try to keep trail of database.

--demo table for AuditTrail

--Note: this is just for demo purpose, in live situation we may have very big table than
-- on explained here. everybody has their own need and situation.
CREATE TABLE AuditTrail(
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT def_AuditTrail_Id DEFAULT(NEWID()),
AuditDate DATETIME,
TableName VARCHAR(50) NOT NULL,

ColumnName VARCHAR(50) NOT NULL,

Description VARCHAR(50) NOT NULL,

UserName VARCHAR(50) NOT NULL,

OldValue VARCHAR(50) NOT NULL,

NewValue VARCHAR(50) NOT NULL

)


--Now we will create one more table for which we will keep the trail in above table
--You can keep Audit of Insert, Delete and Update everything in above table

--and can customize it as per your need

CREATE
TABLE AuditDemo
(

ID INT IDENTITY(1,1) NOT NULL,

Name
VARCHAR(10),

Company VARCHAR(10)


-- have few records in above table

INSERT INTO AuditDemo
SELECT
'Ritesh','eChem' UNION ALL

SELECT 'Rajan','Marwadi'


--LET us create fixed audit trail trigger
create
TRIGGER AUDITonAuditDemo
ON
AuditDemo
AFTER UPDATE

AS

IF
UPDATE(Name)
BEGIN

INSERT
AuditTrail

(
AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)

SELECT

GETDATE
(),'AuditDemo','Name','Update',suser_sname(),DELETED.Name,INSERTED.Name

FROM
INSERTED

JOIN
DELETED

ON
INSERTED.ID=DELETED.ID

END


IF UPDATE(company)
BEGIN

INSERT
AuditTrail

(
AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)

SELECT

GETDATE
(),'AuditDemo','Company','Update',suser_sname(),Deleted.Company,Inserted.Company

FROM
INSERTED

JOIN
DELETED

ON
INSERTED.ID=DELETED.ID

END


--let us update record in AuditDemo tabel

UPDATE AuditDemo SET Name='R.Shah' WHERE ID=2

--LET US NOW CHECK WHETHER WE HAVE TRAILED THE RECORDS OR NOT

SELECT * FROM AuditTrail


--let use update and check once agian

UPDATE AuditDemo SET Company='Testing'

SELECT * FROM AuditTrail

Hope you have enjoyed!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

3 comments:

Asembereng said...

Thanks for the post. But if you want to do this for a table of 20 fields? do u have to do all 20 fields separately?

Ritesh Shah said...

Yes, you have to, unfortunately there is no short way, may be you can do it by some dynamic SQL

Ritesh Shah said...

related post http://www.sqlhub.com/2011/07/audit-trail-with-output-clause-in-sql.html