Skip to content

How To: SQL Server Trigger 101

June 20, 2008

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
1

Next, we create our Test Tables:
1
--- 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
kick it on DotNetKicks.com

About these ads
5 Comments leave one →
  1. Surya sasidhar permalink
    October 4, 2011 4:12 AM

    thank you, for trigger example, i learn it from your sample code.

  2. Ajoybaby permalink
    June 17, 2009 11:17 PM

    i really looking for how to create trigger.its amusing

  3. soscamau permalink
    April 29, 2009 8:23 AM

    thank u its too fine

  4. prabha permalink
    February 1, 2009 11:38 PM

    thank u its too fine

Trackbacks

  1. 2010 in review « Marlon Ribunal's Note On SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 28 other followers

    %d bloggers like this: