How To: SQL Server Trigger 101

Target Audience: Beginners
Applied To: SQL Server 2000/2005/2008

Today, we will learn about the basics of SQL Server Trigger. What we will do as a first step is to build our Test Database. We will also create our main table (Table1), which is the table that we’re creating our Trigger for. Our trigger will trap the Inserts and Deletes and copy these to InsertedTable and DeletedTable, respectively. So, here we go:

Creating The Test Database

-- CREATE OUR TEST DATABASE
CREATE DATABASE Test

-- USE TEST DATABASE
USE Test


Next, we create our Test Tables:

--- CREATE TEST TABLES
---Table1
CREATE TABLE [dbo].[Table1](
[Field1] [VARCHAR] (50) NULL,
[Field2] [VARCHAR] (50)NULL)
---Table InsertedTable
CREATE TABLE [dbo].[InsertedTable](
[DATE] SMALLDATETIME,
[Field1] [VARCHAR] (50) NULL,
[Field2] [VARCHAR] (50) NULL)
---Table DeletedTable
CREATE TABLE [dbo].[DeletedTable](
[DATE] SMALLDATETIME,
[Field1] [VARCHAR] (50) NULL,
[Field2] [VARCHAR] (50) NULL)

Now, after we created the Database and Tables necessary for our exercise, we’re now ready to create a simple Trigger:

Creating The SQL Trigger

--- CREATE TRIGGER
CREATE TRIGGER CatchInsertDelete
ON Table1
FOR INSERT, DELETE
AS
INSERT INTO InsertedTable (DATE,Field1, Field2)
SELECT GETDATE(), Field1, Field2 FROM Inserted
INSERT INTO DeletedTable (DATE, Field1, Field2)
SELECT GETDATE(), Field1, Field2 FROM Deleted
Go

Testing our Trigger

Let us insert new records to Table1:

---Insert New Records To Table1
INSERT INTO Table1 VALUES ('Mike', 'Hotek')
INSERT INTO Table1 VALUES ('Paul','Nielsen')
INSERT INTO Table1 VALUES ('Denny','Cherry')
INSERT INTO Table1 VALUES ('Bill','Gates')
INSERT INTO Table1 VALUES ('John','Doe')
INSERT INTO Table1 VALUES ('Jane','Doe')

Before proceeding, check your InsertedTable. Were the inserted records captured in the table?

The answer should be yes.

Now, we delete the records where Field2 = ‘Doe’. This will delete the records “John Doe” and “Jane Doe”. The deleted records should be trapped in the DeletedTable:

DELETE FROM TABLE1
WHERE Field2 = 'Doe'

Check your DeletedTable. It should now have the two deleted records.

-Marlon Ribunal