s
Contact Login Register
h M

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:

  • Cant be created on temporary tables.
  • Must be created on tables in the current database.
  • Cant be created on Views
  • When a table is dropped, all triggers associated with that table are dropped.

Number of views (75754)/Comments (-)

Tags:
blog comments powered by Disqus

Enter your email below AND grab your spot in our big giveaway!

The winner will receive the entire Data Springs Collection 7.0 - Designed to get your website up and running like a DNN superhero (spandex not included).

  
Subscribe
united luxury shop
konulu porno
deneme bonusu maltcasino bahis siteleri
sex video free download in mobile hot sexy naked indian girls download free xxx video
antalya escort
atasehir eskort antalya escort bahcelievler escort alanya escort atasehir escort
porno izle
gaziantep escort gaziantep escort
escort pendik
izmir escort kayseri escort gaziantep rus escort
vdcasino casino metropol casinomaxi
baybahis
holiganbet
beylikdüzü escort