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:

We create our Test Database:

1. — CREATE OUR TEST DATABASE
2. CREATE DATABASE Test
3.  
4. – USE TEST DATABASE
5. USE Test

Next, we create our Test Tables:

1. — CREATE TEST TABLES
2.  
3. —Table1
4. CREATE TABLE [dbo].[Table1](
5. [Field1] [VARCHAR] (50) NULL,
6. [Field2] [VARCHAR] (50)NULL)
7.  
8. —Table InsertedTable
9. CREATE TABLE [dbo].[InsertedTable](
10. [DATE] SMALLDATETIME,
11. [Field1] [VARCHAR] (50) NULL,
12. [Field2] [VARCHAR] (50) NULL)
13.  
14. —Table DeletedTable
15. CREATE TABLE [dbo].[DeletedTable](
16. [DATE] SMALLDATETIME,
17. [Field1] [VARCHAR] (50) NULL,
18. [Field2] [VARCHAR] (50) NULL)
19.  

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

1. — CREATE TRIGGER
2.  
3. CREATE TRIGGER CatchInsertDelete
4. ON Table1
5.  
6. FOR INSERT, DELETE
7.  
8. AS
9.  
10. INSERT INTO InsertedTable (DATE,Field1, Field2)
11. SELECT GETDATE(), Field1, Field2 FROM Inserted
12.  
13. INSERT INTO DeletedTable (DATE, Field1, Field2)
14. SELECT GETDATE(), Field1, Field2 FROM Deleted
15.  
16. Go
17.  

Testing our Trigger.

Let us insert new records to Table1:

1. —Insert New Records To Table1
2. INSERT INTO Table1 VALUES (‘Mike’, ‘Hotek’)
3. INSERT INTO Table1 VALUES (‘Paul’,‘Nielsen’)
4. INSERT INTO Table1 VALUES (‘Denny’,‘Cherry’)
5. INSERT INTO Table1 VALUES (‘Bill’,‘Gates’)
6. INSERT INTO Table1 VALUES (‘John’,‘Doe’)
7. 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:

1. DELETE FROM TABLE1
2. WHERE Field2 = ‘Doe’

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

-Marlon Ribunal