SQL Server Triggers

Author: Chad Nash/Monday, November 19, 2012/Categories: In The Flow

Rate this article:

No rating

Triggers are a special type of procedure that can be executed in response to one of the following conditions:

INSERT
UPDATE
DELETE

You can create a Trigger to fire a query based on the insertion, modification or deletion of a record against a table. I like to think of a Trigger as an Event Listener.

To demonstrate a Trigger, I will create two tables for the example.

Example SQL Table 1(Table to store Books):

CREATE TABLE Books
(
BookID INT IDENTITY(1,1) PRIMARY KEY,
BookName NVARCHAR(200) NOT NULL,
Genre NVARCHAR(200) NOT NULL,
DateCreated DATETIME DEFAULT GETDATE()
)

Example SQL Table 2(Table to log events):

CREATE TABLE Book_Log
(
LogID INT IDENTITY(1,1) PRIMARY KEY,
Event NVARCHAR(200) NOT NULL,
EventDescription NVARCHAR(200) NOT NULL,
LogDate DATETIME DEFAULT GETDATE()
)

Now you can create a Trigger that listens for specific events on the Books table. Below is the syntax to add a Trigger in SQL Server:

CREATE TRIGGER TR_BookEvent
ON BOOKS
AFTER INSERT, UPDATE, DELETE
AS
INSERT INTO Book_Log(Event, EventDescription)VALUES('Book Affected', 'A Book was Added, Modified, or Deleted')

The Trigger that was created will fire anytime an INSERT, UPDATE OR DELETE statement is ran against the Books table. In the above Trigger example, anytime I INSERT, UPDATE or DELETE a book from the Books table, the following query will be executed:

INSERT INTO Book_Log(Event, EventDescription)
VALUES('Book Affected', 'A Book was Added, Modified, or Deleted')

In essence, a record will be inserted into my Book_Log table.

You can also specify a Trigger to fire on only one event, rather than all three at once:

CREATE TRIGGER TR_BookEvent
ON BOOKS
AFTER INSERT
AS
INSERT INTO Book_Log(Event, EventDescription)
VALUES('INSERT','Book was Created' )

In the above Trigger example, only when an INSERT into the Books table is execute, will a record be logged into my Book_Log table.

Below is the syntax to remove a Trigger:

DROP TRIGGER NameOfTrigger

Important facts on Triggers: