Auditing Through Triggers (SQL Server)

Reprinted with Permission by Quest Software June 2004

---

Auditing Through Triggers
Robert Marda, bigdough.com

Introduction

In this article I will give code examples for a few triggers. For an excellent explanation about triggers in general and how they work in SQL Server 7.0 and SQL Server 2000 please see the following articles written by Brian Kelley: Triggers in SQL Server 7.0 and 2000 - The Common Ground and Triggers in SQL Server 7.0 and 2000 - What's New. The first article will explain the special tables called inserted and deleted.

I believe these examples will work on SQL Server 2000, however they were only tested on SQL Server 7.0. First we need to have a table to work with. Execute this code in Query Analyzer:

CREATE TABLE [Components] (         [Iden] [int] IDENTITY (1, 1) NOT NULL ,         [ComponentName] [varchar] (25) NULL ,         [SerialNumber] [varchar] (25) NULL ,         [Comments] [varchar] (75) NULL ,         [UserName] [varchar] (50) NULL ,         [UpdateDate] [datetime] NULL ,         [UpdatedBy] [varchar] (35) NULL ) ON [PRIMARY] GO  CREATE TABLE [ComponentsDeleted] (         [Iden] [int] NOT NULL ,         [ComponentName] [varchar] (25) NULL ,         [SerialNumber] [varchar] (25) NULL ,         [Comments] [varchar] (75) NULL ,         [UserName] [varchar] (50) NULL ,         [DeletedDate] [datetime] NULL ,         [DeletedBy] [varchar] (35) NULL ,  ) ON [PRIMARY] GO

Insert/Update Triggers

One of the simplest ways to design a trigger is one that fires whenever any column in a table is updated or whenever a row is inserted. The following code can be executed in Query Analyzer to create a trigger that captures the date a row was inserted or updated and the login for the person that made the last change:

CREATE TRIGGER updatedby ON dbo.Components FOR INSERT, UPDATE AS UPDATE c SET UpdateDate = getdate(), UpdatedBy = SYSTEM_USER FROM inserted i INNER JOIN dbo.Components c ON i.Iden = c.Iden

To test the trigger insert some rows into the table Components and then update one or two rows. Now suppose you only need to know when a component is swapped for a similar component. A change in the serial number would indicate such a swap. You could modify the above trigger (using an ALTER TRIGGER command) to only fire when the SerialNumber column is changed. Execute this code:

ALTER TRIGGER updatedby ON dbo.Components FOR INSERT, UPDATE AS  IF UPDATE(SerialNumber) BEGIN       UPDATE c SET UpdateDate = getdate(), UpdatedBy = SYSTEM_USER      FROM inserted i      INNER JOIN dbo.Components c ON i.Iden = c.Iden  END

Test this trigger by updating a value in the column Comments, then in the column SerialNumber.

Delete Trigger

Now you want to capture the components that are deleted from the components table. These could be components being returned. The below trigger will insert a row into ComponentsDeleted whenever a row is deleted from Components:

CREATE TRIGGER deletedby ON dbo.Components FOR DELETE AS INSERT INTO ComponentsDeleted (Iden, ComponentName, SerialNumber, Comments, UserName, DeletedDate, DeletedBy) SELECT Iden, ComponentName, SerialNumber, Comments, UserName, getdate(), SYSTEM_USER FROM deleted

Delete a row or two from table Components. Now take a look at your ComponentsDeleted table. You will find your deleted rows there with the date and time they were deleted.

Conclusions

Setting up a simple auditing system using triggers similar to those described in this article will take very little time and could be useful if you must know when certain actions are performed in your database and by whom.

 

 

From: Gayanga [mailto:Gayanga@hsenid.lk]
Sent: Wednesday, September 21, 2005 1:52 PM
To: developers@hsenid.lk
Subject: Auditing Through Triggers (SQL Server)