ptg
954
CHAPTER 30 Creating and Managing Triggers
trigger and inclusion of the word AFTER in the name of the old trigger have helped accen-
tuate the behavior of the AFTER trigger: the AFTER trigger executes after a data modification
has taken place.
NOTE
Throughout the rest of this chapter, if the trigger type is not specified, you can assume
that it is an AFTER trigger.
The fact that an AFTER trigger fires after a data modification might seem to be a simple
concept, but it is critical to understanding how it works. The AFTER trigger fires after the
data modification statement completes but before the statement’s work is committed to
the databases. The statement’s work is captured in the transaction log but not committed
to the database until the trigger has executed and performed its actions.
The trigger has the capability to roll back its actions as well as the actions of the modifica-
tion statement that invoked it. This is possible because an implicit transaction exists that
includes both the modification statement and trigger it fires. If the trigger does not issue a
rollback, an implicit
COMMIT of all the work is issued when the trigger completes.
The basic syntax for creating an AFTER trigger is as follows:
CREATE TRIGGER trigger_name
ON table_name
AFTER { INSERT | UPDATE | DELETE }
AS
SQL statements
The AFTER trigger is the default type of DML trigger, so the AFTER keyword is optional.
Listing 30.1 shows the code you use to create a trigger in the BigPubs2008 database. (You
can find instructions for creating the BigPubs2008 database in the introduction chapter at
the beginning of this book.) This new trigger prints a message, stating the number of rows
updated by an
UPDATE statement. You then execute a couple of UPDATE statements to see

whether the trigger works.
LISTING 30.1 An Example of a Simple AFTER Trigger
CREATE TRIGGER tr_au_upd ON authors
AFTER UPDATE
AS
PRINT ‘TRIGGER OUTPUT: ‘ +CONVERT(VARCHAR(5), @@ROWCOUNT)
+ ‘ rows were updated.’
GO
UPDATE authors
SET au_fname = au_fname
Download from www.wowebook.com
ptg
955
Using DML Triggers
WHERE state = ‘UT’
GO
TRIGGER OUTPUT: 1 rows were updated.
UPDATE authors
SET au_fname = au_fname
WHERE state = ‘CA’
GO
TRIGGER OUTPUT: 37 rows were updated.
Even though you do not actually change the contents of the au_fname column (because
you set it to itself), the trigger fires anyway. Listing 30.1 does not show the typical use of a
trigger, but it gives you some insight into how and when a trigger fires. The fact that the
trigger fires, regardless of what is updated, causes many developers to test the
@@rowcount
value at the beginning of the trigger code. If @@rowcount is equal to zero, the trigger can
return without executing the remainder of the trigger code. This is a good tactic for opti-
mizing the performance of triggers.

NOTE
Triggers are meant to guarantee the integrity of data. Although you can retur n result sets
and messages in triggers, doing so is not recommended. The programmers who write
applications that perform modifications on a table are probably not prepared to get
unexpected result sets or messages when they submit data modification statements.
The exception is returning an error with the
RAISERROR command. If a trigger performs
ROLLBACK TRAN, it should also execute RAISERROR to communicate the failure to the
application.
Executing AFTER Triggers
You know that the
AFTER trigger fires when a data modification (such as an insertion, an
update, or a deletion) takes place. What about the trigger’s execution in relationship to
other events, including the execution of constraints? The following events take place
before an
AFTER trigger executes:
. Constraint processing—This includes CHECK constraints, UNIQUE constraints, and
PRIMARY KEY constraints.
. Declarative referential actions—These actions are defined by FOREIGN KEY
constraints that ensure the proper relationships between tables. This includes cascad-
ing FOREIGN KEY constraints.
. Triggering action—This data modification caused the trigger to fire. The action
occurs before the trigger fires, but the results are not committed to the database until
the trigger completes.
You need to consider this execution carefully when you design triggers. For example, if
you have a constraint and trigger defined on the same column, any violations to the
30
Download from www.wowebook.com
ptg
956

CHAPTER 30 Creating and Managing Triggers
constraint abort the statement, and the trigger execution does not occur. For example, if
you have a foreign key constraint on a table that ensures referencial integrity and a trigger
that that does some validation on that same foreign key column then the trigger valida-
tion will only execute if the foreign key validation is successful.
Specifying Trigger Firing Order
You can create more than one trigger on a table for each data modification action. In
other words, you can have multiple triggers responding to an
INSERT, an UPDATE, or a
DELETE command. This can be useful in certain situations, but it can generate confusion
because you might not know the order in which the triggers fire for the particular action.
Some of the confusion has been alleviated by the fact that SQL Server 2008 allows you to
specify the first and last trigger that fire for a particular action. If you have four triggers
responding to updates on a given table, you can set the order for two of the triggers (first
and last), but the order of the remaining two triggers remains unknown.
The
sp_settriggerorder procedure is the tool you use to set the trigger order. This proce-
dure takes the trigger name, order value (FIRST, LAST, or NONE), and action (INSERT,
UPDATE, or DELETE) as parameters. For example, you could use the following to set the
firing order on the trigger used in this chapter’s simple example:
sp_settriggerorder tr_au_upd, FIRST, ‘UPDATE’
The execution of this command sets the tr_au_upd trigger as the first trigger to fire when
an update happens to the table on which this trigger has been placed. If an ALTER state-
ment is executed against the trigger after the trigger order has been defined, the firing
order is lost. The
sp_settriggerorder procedure must be run again to reestablish the
firing order.
NOTE
It is recommended that you avoid defining multiple triggers for the same event on the
same table when possible. Often, it is possible to include all the logic in one trigger

defined for an action. This can simplify your database and avoid the uncertainty of the
firing order.
Special Considerations with AFTER Triggers
Following are a few other considerations for
AFTER triggers:
. AFTER triggers can be used on tables that also have cascading referential integrity
constraints. The cascading feature, which was new to SQL Server 2000, allows you to
define cascading actions when a user updates or deletes a primary key to which a
foreign key points. This new feature is discussed in more detail in Chapter 24,
“Creating and Managing Tables.”
.
WRITETEXT and TRUNCATE TABLE do not fire triggers. BCP, by default, does not fire
triggers either, but the FIRE_TRIGGERS bulk copy hint can be specified to cause both
AFTER and INSTEAD OF triggers to execute.
Download from www.wowebook.com
ptg
957
Using DML Triggers
. Triggers are objects, so they must have unique names within the database. If you try
to add a trigger with a name that already exists, you get an error message. You can,
however, use
ALTER on an existing trigger.
The following restrictions apply to AFTER triggers:
. AFTER triggers can be placed only on tables, not on views.
. A single AFTER trigger cannot be placed on more than one table.
. The text, ntext, and image columns cannot be referenced in the AFTER trigger logic.
Using inserted and deleted Tables
In most trigger situations, you need to know what changes were made as part of the data
modification. You can find this information in the inserted and deleted tables. For the
AFTER trigger, these temporary memory-resident tables contain the rows modified by the

statement. With the INSTEAD OF trigger, the inserted and deleted tables are actually
temporary tables created on-the-fly.
The inserted and deleted tables have identical column structures and names as the
tables that were modified. Consider running the following statement against the
BigPubs2008 database:
UPDATE titles
SET price = $15.05
WHERE type LIKE ‘%cook%’
When this statement is executed, a copy of the rows to be modified is recorded, along
with a copy of the rows after the modification. These copies are available to the trigger in
the
deleted and inserted tables.
If you want to be able to see the contents of the deleted and inserted tables for testing
purposes, you can create a copy of the table and then create a trigger on that copy (see
Listing 30.2). You can perform data modification statements and view the contents of
these tables without the modification actually taking place.
LISTING 30.2 Viewing the Contents of the inserted and deleted Tables
Create a copy of the titles table in the BigPubs2008 database
SELECT *
INTO titles_copy
FROM titles
GO
add an AFTER trigger to this table for testing purposes
CREATE TRIGGER tc_tr ON titles_copy
FOR INSERT, UPDATE, DELETE
AS
PRINT ‘Inserted:’
SELECT title_id, type, price FROM inserted
30
Download from www.wowebook.com

ptg
958
CHAPTER 30 Creating and Managing Triggers
PRINT ‘Deleted:’
SELECT title_id, type, price FROM deleted
ROLLBACK TRANSACTION
The inserted and deleted tables are available within the trigger after INSERT, UPDATE, and
DELETE. Listing 30.3 shows the contents of inserted and deleted, as reported by the
trigger when executing the preceding UPDATE statement.
LISTING 30.3 Viewing the Contents of the inserted and deleted Tables When Updating
the titles_copy Table
UPDATE titles_copy
SET price = $15.05
WHERE type LIKE ‘%cook%’
Inserted:
title_id type price

TC7777 trad_cook 15.05
TC4203 trad_cook 15.05
TC3218 trad_cook 15.05
MC3021 mod_cook 15.05
MC2222 mod_cook 15.05
Deleted:
title_id type price

TC7777 trad_cook 14.3279
TC4203 trad_cook 14.595
TC3218 trad_cook 0.0017
MC3021 mod_cook 15.894
MC2222 mod_cook 14.9532

NOTE
In SQL Server 2008, an error message is displayed after a rollback is initiated in a trig-
ger. The error message indicates that the transaction ended in the trigger and that the
batch has been aborted. Prior to SQL Server 2005, an error message was not dis-
played when a rollback was encountered in the trigger.
The nature of the inserted and deleted tables enables you to determine the action that
fired the trigger. For example, when an INSERT occurs, the deleted table is empty because
there were no previous values prior to the insertion. Table 30.1 shows the DML triggering
events and the corresponding contents in the
deleted and inserted tables.
Download from www.wowebook.com
ptg
959
Using DML Triggers
NOTE
Triggers do not fire on a row-by-row basis. One common mistake in coding triggers is to
assume that only one row is modified. However, triggers are set-based. If a single
statement affects multiple rows in the table, the trigger needs to handle the processing
of all the rows that were affected, not just one row at a time.
One common approach to dealing with the multiple rows in a trigger is to place the
rows in a cursor and then process each row that was affected, one at a time. This
approach works, but it can have an adverse effect on the performance of the trigger. To
keep your trigger execution fast, you should try to use rowset-based logic instead of
cursors in triggers when possible.
Rowset-based logic will typically join to the inserted or deleted table that are available
to a trigger. You can join these tables to other tables that are being manipulated by the
trigger. For example, a trigger on a Job table can update a related employee table with
rowset-based logic such as :
UPDATE employee
SET employee.job_lvl = i.min_lvl

FROM inserted i
WHERE employee.emp_id = i.emp_id
This kind of logic will allow the trigger update to work correctly if one job record is
changed or many job rows are changed at once. This is much more efficient than load-
ing all of the rows from the inserted table into a cursor which updates the employee
records one at a time within the cursor loop.
Checking for Column Updates
The UPDATE() function is available inside INSERT and UPDATE triggers. UPDATE() allows a
trigger to determine whether a column was affected by the INSERT or UPDATE statement
that fired the trigger. By testing whether a column was actually updated, you can avoid
performing unnecessary work.
For example, suppose a rule mandates that you cannot change the city for an author (a silly
rule, but it demonstrates a few key concepts). Listing 30.4 creates a trigger for both
INSERT
and UPDATE that enforces this rule on the authors table in the BigPubs2008 database.
30
TABLE 30.1 Determining the Action That Fired a Trigger
Statement
Contents of inserted Contents of deleted
INSERT
Rows added Empty
UPDATE
New rows Old rows
DELETE
Empty Rows deleted
Download from www.wowebook.com
ptg
960
CHAPTER 30 Creating and Managing Triggers
LISTING 30.4 Using the UPDATE() Function in a Trigger

CREATE TRIGGER tr_au_ins_upd ON authors
FOR INSERT, UPDATE
AS
IF UPDATE(city)
BEGIN
RAISERROR (‘You cannot change the city.’, 15, 1)
ROLLBACK TRAN
END
GO
UPDATE authors
SET city = city
WHERE au_id = ‘172-32-1176’
Server: Msg 50000, Level 15, State 1, Procedure
tr_au_ins_upd, Line 5
You cannot change the city.
Listing 30.4 shows how you generally write triggers that verify the integrity of data. If the
modification violates an integrity rule, an error message is returned to the client applica-
tion, and the modification is rolled back.
The
UPDATE() function evaluates to TRUE if you update the column in the UPDATE state-
ment. As shown in the preceding example, you do not have to change the value in the
column for the
UPDATE() function to evaluate to TRUE, but the column must be referenced
in the UPDATE statement. For example, with the author update, the city column was set it
to itself (the value does not change), but the UPDATE() function still evaluates to TRUE.
NOTE
If you created the tr_au_upd trigger on the authors table as part of the AFTER trigger
example earlier in this chapter, you might have also seen the TRIGGER OUTPUT: 1
rows were updated message. This trigger was set to be the first trigger to fire, and it
executes in addition to the new ins_upd trigger added in the example from this section.

Now you can add a couple of INSERT statements on the authors table:
INSERT authors (au_id, au_lname, au_fname, city, contract)
VALUES(‘111-11-1111’, ‘White’, ‘Johnson’,’Menlo Park’, 1)
Results from the previous insert
Server: Msg 50000, Level 15, State 1
You cannot change the city.
Download from www.wowebook.com
ptg
961
Using DML Triggers
The UPDATE() function evaluates to TRUE and displays the error message. This outcome is
expected because the trigger was created for INSERT as well, and the IF UPDATE condition
is evaluated for both insertions and updates.
Now you can see what happens if you change the INSERT statement so that it does not
include the city column in the INSERT:
INSERT authors (au_id, au_lname, au_fname, contract)
VALUES(‘111-11-2222’, ‘White’, ‘Johnson’, 1)
Server: Msg 50000, Level 15, State 1
You cannot change the city.
The error message is still displayed, even though the insertion was performed without the
city column. This process might seem counterintuitive, but the IF UPDATE condition
always returns a TRUE value for INSERT actions. The reason is that the columns have either
explicit default values or implicit (NULL) values inserted, even if they are not specified. The
IF UPDATE conditions see this as a change and evaluate to TRUE.
If you change the tr_au_ins_upd trigger to be for UPDATE only (not INSERT and UPDATE),
the insertions can take place without error.
Enforcing Referential Integrity by Using DML Triggers
Several options, including foreign key constraints and stored procedures, are available to
enforce referential integrity, but using a trigger is still a viable alternative. A trigger
provides a great deal of flexibility and allows you to customize your referential integrity

solution to fit your needs. Some of the other alternatives, such as foreign keys, do not
provide the same degree of customization.
TIP
In a database environment in which multiple databases are used with related data, a
trigger can be invaluable for enforcing referential integrity. The trigger can span data-
bases, and it can ensure that data rows inserted into a table in one database are valid
based on rows in another database.
Listing 30.5 shows how to re-create and populate the customers and orders tables in the
sample BigPubs2008 database.
LISTING 30.5 Creating and Populating the customers and orders Tables
if exists (select * from sysobjects
where id = object_id(‘orders’) and sysstat & 0xf = 3)
drop table orders
GO
if exists (select * from sysobjects
30
Download from www.wowebook.com
ptg
962
CHAPTER 30 Creating and Managing Triggers
where id = object_id(‘customers’) and sysstat & 0xf = 3)
drop table customers
GO
CREATE TABLE customers
(customer_id INT PRIMARY KEY NOT NULL,
customer_name NVARCHAR(25) NOT NULL,
customer_comments NVARCHAR(22) NULL)
CREATE TABLE orders
(order_id INT PRIMARY KEY NOT NULL,
customer_id INT,

order_date DATETIME,
CONSTRAINT FK_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers (customer_id))
INSERT customers (customer_id, customer_name, customer_comments)
VALUES(1, ‘Hardware Suppliers AB’,’Stephanie is contact.’)
INSERT customers (customer_id, customer_name, customer_comments)
VALUES(2, ‘Software Suppliers AB’,’Elisabeth is contact.’)
INSERT customers (customer_id, customer_name, customer_comments)
VALUES(3, ‘Firmware Suppliers AB’,’Mike is contact.’)
INSERT orders (order_id, customer_id, order_date)
VALUES(100, 1, GETDATE())
INSERT orders (order_id, customer_id, order_date)
VALUES(101, 1, GETDATE())
INSERT orders (order_id, customer_id, order_date)
VALUES(102, 1, GETDATE())
SELECT * FROM customers
SELECT * FROM orders
customer_id customer_name customer_comments

1 Hardware Suppliers AB Stephanie is contact.
2 Software Suppliers AB Elisabeth is contact.
3 Firmware Suppliers AB Mike is contact.
order_id customer_id order_date

100 1 2009-06-17 05:16:49.233
101 1 2009-06-17 05:16:49.233
102 1 2009-06-17 05:16:49.233
When foreign key constraints are used to enforce referencial integrity, they prohibit
several different types of changes to the data in the related tables. These restrictions
Download from www.wowebook.com

ptg
963
Using DML Triggers
ensure the that the integrity of the relationships is maintained. For example, the FOREIGN
KEY constraint FK_orders_customers on the orders table prohibits the following:
. Inserting rows into the orders table for customer numbers that don’t exist in the
customers table
. Updating the orders table by changing the customer number to values that don’t
exist in the customers table
. Deleting rows in the customers table for which orders exist
. Updating the customers table by changing the customer number for which orders
exist
You might want a cascading action instead of prohibiting the deletion or update of rows
on the
customers table. This would include automatically cascading the DELETE or UPDATE
statement executed on the customers table to the related orders table. You can do this by
using triggers.
Cascading Deletes
A cascading delete is relatively simple to create. Listing 30.6 shows a cascading delete
trigger for the customers table.
TIP
SQL Server 2000 added a feature that allows you to define cascading actions on a
FOREIGN KEY constraint. When defining the constraints on a table, you can use the ON
UPDATE CASCADE or ON DELETE CASCADE clause, which causes changes to the primary
key of a table to cascade to the related foreign key tables. Refer to Chapter 26,
“Implementing Data Integrity,” for further information on this option.
LISTING 30.6 A Cascading Delete for the customers Table
CREATE TRIGGER cust_del_orders ON customers
FOR DELETE
AS

IF @@ROWCOUNT = 0
RETURN
DELETE orders
FROM orders o , deleted d
WHERE o.customer_id = d.customer_id
IF @@ERROR <> 0
BEGIN
RAISERROR (‘ERROR encountered in cascading trigger.’, 16, 1)
30
Download from www.wowebook.com