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.