Week 3

Day 19
Triggers

Triggers enable SQL Server to assess data modifications before they are permanently written. With a trigger, you can check complex data integrity rules, and you can perform all kinds of dependent data. Today you will learn the following:

•  What triggers are

•  How to write triggers

•  How the system executes triggers

Beyond the simple mechanics of writing triggers, you also learn how triggers work. Armed with that information, you will learn when triggers are the right solution and when they should be avoided.

Understanding Triggers

A trigger is a special kind of stored procedure associated with an action on a table. Although triggers resemble stored procedures, you can’t execute them. Instead, the system automatically executes a trigger after you make a change to a row or rows in a table.

Let’s write a simple trigger. This will show you the syntax of a CREATE TRIGGER statement. Then we’ll execute it. First we need a table to work on. A simple table with just a couple of columns will do. (Go ahead and create the table in tempdb if you need to.)

create  table  t
(c1   int    identity,
  c2  int  not  null,
  c3   char(4)  null)
go
insert  t  (c2, c3)  values  (10,  “xxxx”)
insert  t  (c2, c3)  values  (25,  “yyyy”)
insert  t  (c2) values  (20)
go

A CREATE TRIGGER statement looks like a CREATE PROCEDURE statement, with three exceptions:

•  A trigger is associated with a table, and with one or more actions on that table.

•  A trigger takes no parameters for input or output.

•  The RETURN statement in a trigger cannot include a return status.

Here’s a sample trigger:

create  trigger  tr
on  t
for  insert,  update
as
raiserror  (“%d  rows  modified  (trigger  message)”,  0,  1,  @@rowcount)
return

The trigger executes whenever you insert or update rows in table t. When it executes, it reports the number of rows modified by the statement calling the trigger (@@rowcount), that is, the INSERT or UPDATE statement.

Getting Information on Triggers

Before going into further detail about trigger execution, you need to know how to see what triggers are associated with a table, and to look at the code for those triggers. (You should be able to guess some of this information from your experience with stored procedures.)

To see a list of objects in the database, use sp_help. This list also includes triggers. I’ve trimmed out everything but user objects (no system tables) from this output:

sp_help

Name               Owner           Object_type
--------------     -----------     -----------
t                  dbo             user table
tr                 dbo             trigger

To see only a list of triggers, try this SELECT statement:

select name, user_name(uid), type
from sysobjects
where type = “TR”

name                                                          type
------------------------------ ------------------------------ ----
tr                             dbo                            TR

The problem is that neither of these methods shows you the relationship between triggers and tables. What if you wanted to see a list of tables and any related triggers? Here’s a SELECT statement that does just that:

select   name,
         “instrig”  =  object_name(instrig),
        “updtrig”  =  object_name(updtrig),
         “deltrig”  = object_name(deltrig)
from     sysobjects
where type = “U”
and (instrig <> 0 or updtrig <> 0 or deltrig <> 0)

name      instrig      updtrig      deltrig
--------- ------------ ------------ -----------
t         tr           tr           (null)

The system table, sysobjects, contains a list of all objects, including tables and triggers. The columns instrig, updtrig, and deltrig store the object IDs of triggers for each table. If the trigger ID is 0, there is no trigger for that action for the table.

---

Note:  If you look at the sysobjects table, you will also find a column, seltrig. As intriguing as the possibilities for a select trigger might be, you can’t create one. I have no idea why the column exists at all.

---

Once you know the name of a trigger, you can display the text used to create it with sp_helptext:

sp_helptext tr

text
-----------------------------------------------------------------
create  trigger  tr
on t
for  insert,  update
as
raiserror  (“%d  rows  modified  (trigger  message)”,  0,  1,  @@rowcount)
return

sp_helptext displays the text of the trigger at the time it was created. Remember to store scripts to recreate triggers, procedures, views, and other objects in a separate file system to guarantee that you can recreate the objects successfully after a system failure.

Trigger Maintenance

To drop a trigger, use DROP TRIGGER, which works the same way as other DROP statements. Note the following example:

drop    trigger    tr

This would remove the trigger from the table. After this statement, there would be no INSERT or UPDATE trigger for the table.

You can replace a trigger with another (differently named) one without first dropping the old trigger. The new trigger will replace the old one only for the actions specified. This trigger replaces the update action for the trigger you originally created:

create   trigger    trX
on   t
for    insert
as
“insert   trigger   fired”
return

At this point, the new trigger is responsible for handling inserts, but the old one still handles updates.