An Introduction to Triggers -- Part II

By Garth Wells on 4 November 2001 | 1 Comment | Tags: Triggers

Article Series Navigation:

A few months ago I wrote an article for SQLTeam called An Introduction to Triggers -- Part I. The article covered trigger fundamentals--the most important being the way the inserted and deleted virtual tables work. I also showed an example of how to use an INSERT trigger to log activity on a web site. In this article I want to show how to use an UPDATE and DELETE trigger and introduce INSTEAD OF triggers. If you are not familiar with the inserted and deleted tables please read this article before continuing.

UPDATE Trigger

An UPDATE trigger is used to perform an action after an update is made on a table. The example shown here is based on the table used in Part I of this article.

ON Orders

--Make sure Priority was changed
IF NOT UPDATE(Ord_Priority)

--Determine if Priority was changed to high
           FROM inserted a
	   JOIN deleted b ON a.Ord_ID=b.Ord_ID
	   WHERE b.Ord_Priority <> 'High' AND
	   a.Ord_Priority = 'High')
  DECLARE @Count tinyint
  SET @Count = (SELECT COUNT(*)
                FROM inserted a
		JOIN deleted b ON a.Ord_ID=b.Ord_ID
		WHERE b.Ord_Priority <> 'High' AND
		a.Ord_Priority = 'High')
  PRINT CAST(@Count as varchar(3))+' row(s) where changed to a priority of High'

In Part I the INSERT trigger watched for orders with a priority of 'High.' The UPDATE trigger watches for orders whose priority are changed from something else to High.

The IF statement checks to see if the Ord_Priority value was changed. If not we can save some time by exiting the trigger immediately.

The deleted table holds the pre-UPDATE values and inserted table holds the new values. When the tables are joined it is easy to tell when the priority changes from something else to High.

DELETE Trigger

Once you understand how an UPDATE trigger works a DELETE trigger is easy to implement. In the example shown here it simply counts the number of rows in the deleted table to see how many had a priority of high.

ON Orders

--Determine if Order with a Priority of High was deleted
IF EXISTS (SELECT * FROM deleted WHERE Ord_Priority = 'High')
  DECLARE @Count tinyint
  SET @Count = (SELECT * FROM deleted WHERE Ord_Priority = 'High')
  PRINT CAST(@Count as varchar(3))+' row(s) where deleted whose priority was High'


INSTEAD OF triggers are new to SQL Server 2000. The main reason they were introduced is to facilitate updating Views. I am going to show you how one works, but I am not going to bother with updating a View. Quite frankly I think updating Views is poor application design. I was once told by one of my editors that some of my views on Views reflect my "insular" experience, so if you do not agree with me on this point rest assured you are not alone.

An INSTEAD OF Trigger is used to perform an action instead of the one that caused the trigger to be fired. This sounds like double-talk, so I will explain a little more. Let's say you have an INSTEAD OF INSERT trigger defined on a table and an INSERT is executed. A row is not added to the table, but the code in the trigger is fired. The following shows what this looks like.

ON Orders
PRINT 'Updateable Views are Messy'

The following INSERT produces the message shown, but the row is not added to the table.

INSERT Orders (Ord_Priority) VALUES ('High')

-- Results --

Updateable Views are Messy

Feel free to experiment with this type of trigger, but unless you are trying to update a View that is based on multiple tables I do not think it will be of much practical value.

Need More Information on Views?

If you would like more information on Views you can read a free chapter I have listed on my web site. Go to and click on the Sample Chapters section. After reading the chapter you will really know how I feel about this pathetic excuse for a database object (just kidding).

< Previous: An Introduction to Triggers -- Part I | Next >

Discuss this article: 1 Comment so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Using DDL Triggers in SQL Server 2005 to Capture Schema Changes (13 August 2007)

Audit Triggers for SQL Server (8 May 2002)

Code to find out the statement that caused the trigger to fire! (16 April 2002)

An Introduction to Triggers -- Part I (30 April 2001)

Replicating Triggers (14 August 2000)

Other Recent Forum Posts

How to Download Ringtones (1 Reply)

Database Diagram not showing relationship of table (3 Replies)

SQL2008R2: Should there be a limit xmla qry size? (3 Replies)

Variables in Where Clause (20 Replies)

how to update 2 records on 1 field (20 Replies)

How to migrate Replication DB on sql server 2008 (7 Replies)

Reading XML in SQL (5 Replies)

Return a zero value in place of null (8 Replies)

Subscribe to

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers. Articles via RSS Weblog via RSS

- Advertisement -