Binaryworld - How to create audit triggers? ... [ SQL Server -> Server Operations ]

This article will show you the most easiest way to audit your data changes for a specific table in sql server.

I will show you, how to use DBCC INPUTBUFFER command to capture some valuable audit information when update/insert/delete occurs.

Step-By-Step Example

- Run the following script in query analyzer. This will create an audit log table and an audit trigger on northwind.dbo.categories table

Click here to copy the following block

Use Northwind
go

--///////////////////////
--//Create Audit log table
--///////////////////////
Create table _AuditInfo
(
AuditId int identity(1,1) PRIMARY KEY
,tablename varchar(128)
,Qry varchar(500)
,LoginName varchar(500)
,UserName varchar(500)
,cdate datetime default(getdate())
)

go

/*

Audit trigger by nayan patel
website : www.binaryworld.net

*/

Create trigger tr_AUDIT_on_Categories_table on dbo.Categories
INSTEAD OF Update,Delete,Insert
As

set transaction isolation level read uncommitted
set nocount on

DECLARE @cmd varchar(8000)
DECLARE @ExecStr varchar(50)
DECLARE @Qry varchar(255)
Declare @LoginName varchar(255)
Declare @UserName varchar(255)
Declare @dt datetime

SET @Qry=''
SET @LoginName=SYSTEM_USER
SET @UserName=USER

--//find about what/who/when fired the trigger

CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)

SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'

INSERT INTO #inputbuffer
EXEC (@ExecStr)

SET @Qry = (SELECT EventInfo FROM #inputbuffer)
SET @LoginName=SYSTEM_USER
SET @UserName=USER
SET @dt= CURRENT_TIMESTAMP

Print 'Qry : ' + @Qry
Print 'LoginName : ' + @LoginName
Print 'UserName : ' + @UserName
Print 'TimeStamp : ' + cast(@dt as varchar(25))

INSERT INTO _AuditInfo(tablename,Qry,LoginName,UserName,cdate) VALUES('Northwind.dbo.Categories',@Qry,@LoginName,@UserName,@dt)

go

--///////////////////////
--// Do some updates
--///////////////////////
Update dbo.Categories set Categoryname=Categoryname where 1=1
Update dbo.Categories set Categoryname=Categoryname where 2=2

go

--///////////////////////
--//Display Audit table
--///////////////////////
select * from _AuditInfo

go