Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Auditing Through Triggers

By Robert Marda,

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.
Total article views: 16508 | Views in the last 30 days: 7
 
Related Articles
FORUM

Trigger defined for Insert, Update

Determine Update or Insert for Trigger defined for Insert, Update

FORUM

Insert trigger not working when update trigger enabled

Update interfering with insert trigger.

FORUM

not updating inserted row in an insert trigger

not updating inserted row in an insert trigger

FORUM

Updating varchar(max) column

Updating varchar(max) column

FORUM

update trigger

update trigger with like statment

Tags
sql server 7    
triggers    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones