ptg
964
CHAPTER 30 Creating and Managing Triggers
ROLLBACK TRAN
RETURN
END
The following DELETE statement deletes the row for Customer 1, so all three rows for that
customer in the orders table should be deleted by the trigger:
DELETE customers WHERE customer_id = 1
Server: Msg 547, Level 16, State 1
The DELETE statement conflicted with COLUMN REFERENCE
constraint ‘FK_orders_customers’.
The conflict occurred in database ‘BigPubs2008’,
table ‘orders’, column ‘customer_id’.
The statement has been terminated.
This result might not be what you expected. The FOREIGN KEY constraint here restricts the
DELETE statement, so the trigger never fires. The trigger in this example is an AFTER trigger.
Therefore, the trigger does not fire, and the cascading action never takes place. You have
several options to get around this situation:
. Remove the FOREIGN KEY constraint from orders to customers.
. Disable the FOREIGN KEY constraint from orders to customers.
. Keep the FOREIGN KEY constraint and perform all cascading actions in stored
procedures.
. Keep the FOREIGN KEY constraint and perform all cascading actions in the application.
. Use an INSTEAD OF trigger in place of the AFTER trigger.
. Use the new cascading referential integrity constraints.
Listing 30.7 shows how you can disable the FOREIGN KEY constraint so that a cascading
delete can occur.
LISTING 30.7 Disabling the FOREIGN KEY Constraint to the customers Table So That a
Cascading Delete Can Occur
ALTER TABLE orders

NOCHECK CONSTRAINT FK_orders_customers
GO
GO
DELETE customers WHERE customer_id = 1
SELECT * FROM customers
SELECT * FROM orders
customer_id customer_name customer_comments
Download from www.wowebook.com
ptg
965
Using DML Triggers

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

In Listing 30.7, the cascading deletes occur via the trigger because the FOREIGN KEY
constraint is disabled. Compared to a trigger for cascading deletes, a trigger for cascading
updates is more complex and not as common. This issue is discussed in more detail in the
next section.
If you disable the
FOREIGN KEY constraint, you have a potential integrity problem. If rows
are inserted or updated in the orders table, there are no constraints to ensure that the
customer number exists in the customer table. You can take care of this situation by using
an INSERT and UPDATE trigger on the orders table (see Listing 30.8). The trigger in Listing
30.8 tests for the existence of a customer before the order is inserted or updated.
LISTING 30.8 Handling a Restriction by Using a Trigger on the orders Table
if exists (select * from sysobjects where id = object_id(‘dbo.ord_ins_upd_cust’)
and sysstat & 0xf = 8)
drop trigger dbo.ord_ins_upd_cust

GO
CREATE TRIGGER ord_ins_upd_cust ON orders
FOR INSERT, UPDATE
AS
IF EXISTS (SELECT * FROM inserted
WHERE customer_id NOT IN
(SELECT customer_id FROM customers))
BEGIN
RAISERROR(‘No customer with such customer number’, 16, 1)
ROLLBACK TRAN
RETURN
END
Cascading Updates
A cascading update with a trigger is a bit tricky to achieve. Modifying a primary key, per
definition, really involves deleting a row and inserting a new row. The problem is that you
lose the connection between the old row and new row in the
customers table. How do
you know which changes to cascade to which rows?
30
Download from www.wowebook.com
ptg
966
CHAPTER 30 Creating and Managing Triggers
This situation is simpler if you can restrict the changes to one row (see Listing 30.9)
because you have only one row in the deleted and inserted tables. You know the
customer number before and after the modification.
LISTING 30.9 A Cascading Update in a Trigger
if exists (select * from sysobjects where id = object_id(‘dbo.cust_upd_orders’)
and sysstat & 0xf = 8)
drop trigger dbo.cust_upd_orders

GO
CREATE TRIGGER cust_upd_orders ON customers
FOR UPDATE
AS
DECLARE @rows_affected int, @c_id_before int, @c_id_after int
SELECT @rows_affected = @@ROWCOUNT
IF @rows_affected = 0
RETURN No rows changed, exit trigger
IF UPDATE(customer_id)
BEGIN
IF @rows_affected = 1
BEGIN
SELECT @c_id_before = customer_id FROM deleted
SELECT @c_id_after = customer_id FROM inserted
UPDATE orders
SET customer_id = @c_id_after
WHERE customer_id = @c_id_before
END
ELSE
BEGIN
RAISERROR (‘Cannot update more than 1 row.’, 16, 1)
ROLLBACK TRAN
RETURN
END
END
If several rows are updated, it’s not easy to know which order belongs to which customer.
You can easily modify the trigger shown in Listing 30.9 to handle a situation in which
several rows change to the same value; however, this is not allowed because of the
primary key on the
customers table. Instances in which several rows are modified and the

primary key value is changed are rare, and you are not likely to encounter such situations.
Download from www.wowebook.com
ptg
967
Using DML Triggers
NOTE
The cascading FOREIGN KEY constraints are an excellent alternative to triggers, and
they are efficient. If you choose not to use the cascading feature, you might still want
to enjoy the simplicity of constraints. Then you need to handle cascading actions only
in stored procedures or in client applications.
Stored procedures are often a good choice because they essentially give application
developers a function-based interface for modifications. If the implementation details
(for example, the table structure or rules) change, client applications can be isolated
from the changes, as long as the interfaces to the stored procedures stay the same.
The question of how to handle a cascade is a matter of personal preference, however.
Handling cascading updates in a client application or stored procedure is a chicken-
and-egg situation: you cannot change the primary key table first because other tables
reference it. You also cannot change the referencing table because no row exists in the
primary key table with a corresponding value. The solution is to insert in the referenced
table a new row that contains the new primary key value, change the referencing rows,
and then delete the old row from the referenced table.
INSTEAD OF Triggers
SQL Server 2000 introduced a type of trigger called an INSTEAD OF trigger. This type of
trigger extends SQL Server’s trigger capabilities and provides an alternative to the AFTER
trigger that was heavily utilized in prior versions of SQL Server.
The name of the trigger gives you some insight into how this trigger operates: this trigger
performs its actions instead of the action that fired it. This is much different from the
AFTER trigger, which performs its actions after the statement that caused it to fire has
completed. This means you can have an INSTEAD OF update trigger on a table that success-
fully completes but does not include the actual update to the table.

The basic syntax for creating an INSTEAD OF trigger is as follows:
CREATE TRIGGER trigger_name
ON table_name
INSTEAD OF { INSERT | UPDATE | DELETE }
AS
SQL statements
Listing 30.10 shows how to create a trigger that prints a message stating the number of
rows updated by an UPDATE statement. It then executes an UPDATE against the table that
has the trigger on it. Finally, it selects the rows from the table for review.
30
Download from www.wowebook.com
ptg
968
CHAPTER 30 Creating and Managing Triggers
LISTING 30.10 A Simple INSTEAD OF Trigger
if exists (select * from sysobjects where id = object_id(‘dbo.cust_upd_orders’)
and sysstat & 0xf = 8)
drop trigger dbo.cust_upd_orders
GO
CREATE TRIGGER trI_au_upd ON authors
INSTEAD OF UPDATE
AS
PRINT ‘TRIGGER OUTPUT: ‘
+CONVERT(VARCHAR(5), @@ROWCOUNT) + ‘ rows were updated.’
GO
UPDATE authors
SET au_fname = ‘Rachael’
WHERE state = ‘UT’
GO
TRIGGER OUTPUT: 1 rows were updated.

SELECT au_fname, au_lname FROM authors
WHERE state = ‘UT’
GO
au_fname au_lname

Johann Wolfgang von Goethe
As you can see from the results of the SELECT statement, the first name (au_fname) column
is not updated to ’Rachael’. The UPDATE statement is correct, but the INSTEAD OF trigger
logic does not apply the update from the statement as part of its INSTEAD OF action. The
only action the trigger carries out is to print its message.
The important point to realize is that after you define an INSTEAD OF trigger on a table,
you need to include all the logic in the trigger to perform the actual modification as well
as any other actions that the trigger might need to carry out.
Executing INSTEAD OF Triggers
To gain a complete understanding of the
INSTEAD OF trigger, you must understand its
execution in relationship to the other events that are occurring. The following key events
are important when the
INSTEAD OF trigger fires:
. Triggering action—The INSTEAD OF trigger fires instead of the triggering action. As
shown earlier, the actions of the INSTEAD OF trigger replace the actions of the origi-
nal data modification that fired the trigger.
. Constraint processing—Constraint processing—including CHECK constraints,
UNIQUE constraints, and PRIMARY KEY constraints—happens after the INSTEAD OF
trigger fires.
Download from www.wowebook.com
ptg
969
Using DML Triggers
Listing 30.11 demonstrates the trigger execution order.

LISTING 30.11 INSTEAD OF Trigger Execution
CREATE TRIGGER employee_insInstead
ON employee
INSTEAD OF insert
AS
DECLARE @job_id smallint
Insert the jobs record for the employee if it does not already exist
IF NOT EXISTS
(SELECT 1
FROM jobs j, inserted i
WHERE i.job_id = j.job_id)
BEGIN
INSERT jobs
(job_desc, min_lvl, max_lvl)
SELECT ‘Automatic Job Add’, i.job_lvl, i.job_lvl
FROM inserted i
Capture the identify value for the job just inserted
This will be used for the employee insert later
SELECT @job_id = @@identity
PRINT ‘NEW job_id ADDED FOR NEW EMPLOYEE:’ + convert(char(3),@job_id)
END
Execute the original insert action with the newly added job_id
INSERT employee
(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)
SELECT emp_id, fname, minit, lname, @job_id, job_lvl, pub_id, hire_date
FROM Inserted
GO
The trigger in Listing 30.11 can be created in BigPubs2008. The key feature of this INSTEAD
OF trigger is that it can satisfy a referential integrity constraint that was not satisfied before
the INSERT was executed. Note the FOREIGN KEY constraint on the employee table that

references job_id on the jobs table. The trigger first checks whether the jobs record asso-
ciated with the job_id of the employee being inserted exists. If the jobs record does not
30
Download from www.wowebook.com
ptg
970
CHAPTER 30 Creating and Managing Triggers
exist for the inserted employee’s job_id, the trigger inserts a new jobs record and uses it
for the insertion of the employee record.
If you execute the following INSERT statement, which has a job_id that does not exist,
it succeeds:
INSERT EMPLOYEE
(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)
VALUES (‘KNN33333F’, ‘Kayla’, ‘N’, ‘Nicole’, 20, 100, 9952, getdate())
Go
This statement succeeds because the constraint processing happens after the INSTEAD OF
trigger completes its actions. Conversely, if you were to create the same trigger as an AFTER
trigger, the FOREIGN KEY constraint would execute before the AFTER trigger, and the follow-
ing error message would be displayed:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
‘FK__employee__job_id__1BFD2C07’. The
conflict occurred in database ‘BigPubs2008’, table ‘jobs’, column ‘job_id’.
>The statement has been terminated.
Notice with the previous INSTEAD OF trigger example that the last action the trigger
performs is the actual insertion of the employee record. The trigger was created to fire
when an employee was inserted, so the trigger must perform the actual insertion. This
insertion occurs in addition to any other actions that justify the trigger’s creation.
Using AFTER Versus INSTEAD OF Triggers
Now that you have seen some of the key differences between
AFTER and INSTEAD OF trig-

gers, you need to decide which trigger to use. In the previous example (Listing 30.11), the
INSTEAD OF trigger is the only trigger option for this kind of functionality. However, you
can often use either trigger type to attain the same result.
Something you should consider when choosing one of these triggers is the efficiency of
the overall modification. For example, if you have a modification that will cause a trigger
to fire and often reject the modification, you might want to consider using the
INSTEAD
OF trigger. The rationale is that the INSTEAD OF trigger does not perform the actual modifi-
cation until after the trigger completes, so you do not need to undo the modification. If
you were to use an
AFTER trigger in the same scenario, any modifications that were
rejected would need to be rolled back because they have already been written to the trans-
action log by the time the
AFTER trigger fires.
Conversely, if you have a situation in which the vast majority of the updates are not
rejected, the AFTER trigger might be your best choice.
The particular situation dictates the preferred trigger, but you should keep in mind that
INSTEAD OF triggers tend to be more involved than AFTER triggers because an INSTEAD OF
trigger must perform the actual data modification that fired it.
Download from www.wowebook.com
ptg
971
Using DML Triggers
Using AFTER and INSTEAD OF Triggers Together
An important consideration when coding an INSTEAD OF trigger is that it can exist on the
same table as an AFTER trigger. INSTEAD OF triggers can also execute based on the same
data modifications as AFTER triggers.
Consider, for example, the INSTEAD OF trigger from Listing 30.11 that you placed on the
employee table in the BigPubs2008 database. An AFTER trigger already existed on the
employee table. Listing 30.12 shows the code for the existing AFTER trigger on the

employee table.
LISTING 30.12 An AFTER Trigger Placed on the Same Table as an INSTEAD OF Trigger
if exists (select * from sysobjects where id = object_id(‘dbo.employee_insupd’)
and sysstat & 0xf = 8)
drop trigger dbo.employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
Get the range of level for this job type from the jobs table.
declare @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
select @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
from employee e, jobs j, inserted i
where e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
begin
raiserror (‘Job id 1 expects the default level of 10.’,16,1)
ROLLBACK TRANSACTION
end
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
begin
raiserror (‘The level for job_id:%d should be between %d and %d.’,

16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
End
go
30
Download from www.wowebook.com
ptg
972
CHAPTER 30 Creating and Managing Triggers
This AFTER trigger checks whether the job level assigned to the employee falls within a
valid range for the job_id assigned to the employee. It is fired for both insertions and
updates, and it can exist on the same table as the employee_insInstead INSTEAD OF
trigger described earlier. The combined effect on an employee insertion with both the trig-
gers on the employee table is to have the following actions happen:
1. The INSERT data modification is executed.
2. The INSTEAD OF trigger fires, completes its validation, and ultimately does the
employee insertion that is written to the transaction log.
3. Constraint processing completes.
4. The
AFTER trigger fires, performing its actions on the employee record inserted by
the INSTEAD OF trigger.
5. The AFTER trigger completes and commits the transaction to the database.
One of the key points in this example is that the AFTER trigger performs its actions on the
row inserted by the INSTEAD OF trigger. It does not use the record from the original INSERT
that started the trigger execution. Therefore, in this chapter’s example, where the INSTEAD
OF trigger generates a new job_id, the new job_id value—not the job_id that was origi-
nally inserted—is used in the AFTER trigger.
You need to consider rollback and recovery in this scenario as well, but they are beyond
the scope of this discussion. This example simply shows that INSTEAD OF and AFTER trig-
gers can be combined and that you need to consider the order of execution when design-

ing a trigger solution.
Using Views with INSTEAD OF Triggers
One of the most powerful applications of an
INSTEAD OF trigger is to a view. The INSTEAD
OF trigger, unlike the AFTER trigger, can be applied to a view and triggered based on modi-
fications to the view. For more information on views, see Chapter 27, “Creating and
Managing Views.”
The creation of
INSTEAD OF triggers on views is important because data modifications
against views have many restrictions. The list is extensive, but following are a few examples:
. You cannot use data modification statements that apply to more than one table in
the view in a single statement.
. All columns defined as
NOT NULL in the underlying tables that are being inserted
must have the column values specified in the INSERT statement.
. If the view was defined with the WITH CHECK OPTION clause, rows cannot be modi-
fied in a way that will cause them to disappear from the view.
You can use the
INSTEAD OF trigger to overcome some of these restrictions. In particular,
the first restriction (related to making a single table modification) can be addressed with
the
INSTEAD OF trigger. The INSTEAD OF trigger fires before the actual modification takes
place, so it can resolve the modifications to the underlying tables associated with the view.
It can then execute the modification directly against those base tables. The following
example demonstrates this capability:
Download from www.wowebook.com
ptg
973
Using DML Triggers
Use BigPubs2008

go
CREATE VIEW employeeJobs
AS
select j.min_lvl, j.max_lvl, j.job_id, j.job_desc, e.job_lvl, e.emp_id
from employee e, jobs j
where e.job_id = j.job_id
GO
This example creates a view in the BigPubs2008 database that joins data from the
employee and jobs tables. It retrieves the job types and the associated levels, the employ-
ees assigned to the job types, and each employee’s current job level. Following is a sample
set of rows from the view:
min_lvl max_lvl job_id job_desc job_lvl emp_id

25 100 14 Designer 35 ENL44273F
25 100 14 Designer 89 PSA89086M
25 100 14 Designer 100 KFJ64308F
25 100 12 Editor 32 Y-L77953M
25 100 12 Editor 35 H-B39728F
25 100 12 Editor 100 HAS54740M
Let’s say you want to change the minimum job level (min_lvl) for the Designer job to 40
and at the same time set the job level (job_lvl) for any employees who have this job to
40. If you execute the following update—without an INSTEAD OF trigger—against the view,
you get the message shown:
UPDATE employeeJobs
SET min_lvl = 40,
job_lvl = 40
WHERE job_id = 12
GO
View or function ‘employeeJobs’ is not updateable
because the modification affects multiple base tables.

To get around this problem, you can use an INSTEAD OF trigger. The trigger can decipher
the update to the view and apply the updates to the base table without causing the error.
This functionality is demonstrated in the
INSTEAD OF trigger shown in Listing 30.13.
LISTING 30.13 A Basic View with an INSTEAD OF Trigg er
CREATE TRIGGER employeeJobs_updInstead
ON employeeJobs
INSTEAD OF UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
30
Download from www.wowebook.com