Triggers | Inside Microsoft SQL Server 2000

A trigger is a special type of stored procedure that is fired on an event-driven basis rather than by a direct call. Here are some common uses for triggers:

To maintain data integrity rules that extend beyond simple referential integrity
To implement a referential action, such as cascading deletes
To maintain an audit record of changes
To invoke an external action, such as beginning a reorder process if inventory falls below a certain level or sending e-mail or a pager notification to someone who needs to perform an action because of data changes

You can set up a trigger to fire when a data modification statement is issued—that is, an INSERT, UPDATE, or DELETE statement. SQL Server 2000 provides two types of triggers: after triggers and instead-of triggers.

After Triggers

After triggers are the default type of trigger in SQL Server 2000, so you don't have to use the word AFTER in the CREATE TRIGGER statement. In addition, if any documentation discusses "triggers" without specifying after or instead-of triggers, you can assume that the discussion is referring to after triggers.

You can define multiple after triggers on a table for each event, and each trigger can invoke many stored procedures as well as carry out many different actions based on the values of a given column of data. However, you have only a minimum amount of control over the order in which triggers are fired on the same table.

For example, if you have four INSERT triggers on the inventory table, you can determine which trigger will be the first and which will be the last, but the other two triggers will fire in an unpredictable order. Controlling trigger order is a feature that is needed for some of SQL Server's own internal functionality. For example, when you set up merge replication, you can designate a trigger as the first trigger to fire. In general, I recommend that you don't create multiple triggers with any expectation of a certain execution order. If it's crucial that certain steps happen in a prescribed order, you should reconsider whether you really need to split the steps into multiple triggers.

You can create a single after trigger to execute for any or all the INSERT, UPDATE, and DELETE actions that modify data. Currently, SQL Server offers no trigger on a SELECT statement because SELECT does not modify data. In addition, after triggers can exist only on base tables, not on views. Of course, data modified through a view does cause an after trigger on the underlying base table to fire. As you'll see, you can define instead-of triggers on views.

An after trigger is executed once for each UPDATE, INSERT, or DELETE statement, regardless of the number of rows it affects. Although some people assume that a trigger is executed once per row or once per transaction, these assumptions are incorrect, strictly speaking. Let's see a simple example of this. The script below creates and populates a small table with three rows. It then builds a trigger on the table to be fired when a DELETE statement is executed for this table:

 CREATE TABLE test_trigger (col1 int, col2 char(6) ) GO INSERT INTO test_trigger VALUES (1, 'First') INSERT INTO test_trigger VALUES (2, 'Second') INSERT INTO test_trigger VALUES (3, 'Third') INSERT INTO test_trigger VALUES (4, 'Fourth') INSERT INTO test_trigger VALUES (5, 'Fifth') GO CREATE TRIGGER delete_test ON test_trigger AFTER DELETE AS PRINT 'You just deleted a row!' GO

Now let's put the trigger to the test. What do you think will happen when the following statement is executed?

 DELETE test_trigger WHERE col1 = 0

If you execute this statement, you'll see the following message:

 You just deleted a row! (0 row(s) affected)

The message appears because the DELETE statement is a legal DELETE from the test_trigger table. The trigger is fired once no matter how many rows are affected, even if the number of rows is 0! To avoid executing code that is meaningless, it is not uncommon to have the first statement in a trigger check to see how many rows were affected. We have access to the @@ROWCOUNT function, and if the first thing the trigger does is check its value, it will reflect the number of rows affected by the data modification statement that caused the trigger to be fired. So we could change the trigger to something like this:

 ALTER TRIGGER delete_test ON test_trigger AFTER DELETE AS IF @@ROWCOUNT = 0 RETURN PRINT 'You just deleted a row!'

The trigger will still fire, but it will end almost as soon as it begins. You can also inspect the trigger's behavior if many rows are affected. What do you think will happen when the following statement is executed?

 DELETE test_trigger

A DELETE without a WHERE clause means that all the rows will be removed, and we get the following message:

 You just deleted a row! (5 row(s) affected)

Again, the trigger was fired only once, even though more than one row was affected.

If a statement affects only one row or is a transaction unto itself, the trigger will exhibit the characteristics of per-row or per-transaction execution. For example, if you set up a WHILE loop to perform an UPDATE statement repeatedly, an update trigger would execute each time the UPDATE statement was executed in the loop.

An after trigger fires after the data modification statement has performed its work but before that work is committed to the database. Both the statement and any modifications made in the trigger are implicitly a transaction (whether or not an explicit BEGIN TRANSACTION was declared). Therefore, the trigger can roll back the data modifications that caused the trigger to fire. A trigger has access to the before image and after image of the data via the special pseudotables inserted and deleted. These two tables have the same set of columns as the underlying table being changed. You can check the before and after values of specific columns and take action depending on what you encounter. These tables are not physical structures—SQL Server constructs them from the transaction log. In fact, you can think of the inserted and deleted tables as views of the transaction log. For regular logged operations, a trigger will always fire if it exists and has not been disabled. You can disable a trigger by using the DISABLE TRIGGER clause of the ALTER TABLE statement.

You cannot modify the inserted and deleted pseudotables directly because they don't actually exist. As I mentioned earlier, the data from these tables can only be queried. The data they appear to contain is based entirely on modifications made to data in an actual, underlying base table. The inserted and deleted pseudotables will contain as many rows as the insert, update, or delete statement affected. Sometimes it is necessary to work on a row-by-row basis within the pseudotables, although, as usual, a set-based operation is generally preferable to row-by-row operations. You can perform row-by-row operations by executing the underlying insert, update, or delete in a loop so that any single execution affects only one row, or you can perform the operations by opening a cursor on one of the inserted or deleted tables within the trigger.

Specifying Trigger Firing Order

As I mentioned earlier, in most cases you shouldn't manipulate the trigger firing order. However, if you do want a particular trigger to fire first or fire last, you can use the sp_settriggerorder procedure to indicate this ordering. This procedure takes a trigger name, an order value (FIRST, LAST, or NONE), and an action (INSERT, UPDATE, or DELETE) as arguments. Setting a trigger's order value to NONE removes the ordering from a particular trigger. Here's an example:

 sp_settriggerorder delete_test, first, 'delete'

Note that you'll get an error if you specify an action that is not associated with the particular trigger—for example, if the above statement had tried to make delete_test the first "update" trigger. You'll also get an error if you attempt any of the following:

Setting a trigger order for an instead-of trigger.
Specifying an order for a trigger when a trigger already exists for that action in that position. (For example, if there is already a first delete trigger for a table, you must set the order value of that trigger to NONE before you can define a different trigger as the first delete trigger.)
Attempting to give the same trigger both the first and last order value for the same action. One trigger can have two different order values, but only if the trigger is defined for two different actions. You must execute sp_settriggerorder multiple times to accomplish this. Here's an example:
```
 CREATE TRIGGER delete_update_test ON test_trigger AFTER DELETE, UPDATE AS PRINT 'You just deleted or updated a row!' GO EXEC sp_settriggerorder delete_update_test, first, 'delete' EXEC sp_settriggerorder delete_update_test, last, 'update' 
```

Information about trigger type and firing order is stored in the status column of the sysobjects table. Nine of the bits are needed to keep track of this information. Three bits are used to indicate whether the trigger is an insert, update, or delete trigger, three are used for the first triggers for each action, and three are used for the last triggers for each action. Table 12-2 shows which bits correspond to which properties.

Table 12-2. Bits in sysobjects.status that indicate a trigger's functionality.

Bit	Decimal Value	Meaning
8	256	Delete Trigger
9	512	Update Trigger
10	1024	Insert Trigger
14	16384	First Delete Trigger
15	32768	Last Delete Trigger
16	65536	First Update Trigger
17	131072	Last Update Trigger
18	262144	First Insert Trigger
19	524288	Last Insert Trigger

To check to see whether a trigger has a particular functionality, you can decode the sysobjects.status value or use the OBJECTPROPERTY function. There are nine property functions, which correspond to the nine bits indicated in Table 12-2. For example, to check to see whether a trigger is the last update trigger for its table, you would execute this:

 SELECT OBJECTPROPERTY(object_id('delete_update_test'), 'ExecIsLastUpdateTrigger')

A value of 1 means that the trigger has this property, a value of 0 means it doesn't, and a value of NULL means you typed something wrong or the object is not a trigger. However, to find all the properties that a trigger has would mean executing this statement nine times, checking nine different property values. To simplify the process, I created a new version of the sp_helptrigger procedure called sp_helptrigger2(available on the companion CD), which provides ordering information for each of the triggers. Here's an example of its use:

 EXEC sp_helptrigger2 test_trigger PARTIAL RESULTS: trigger_name IsUpdate UpdateOrd IsDelete DeleteOrd IsInsert InsertOrd ------------------ -------- --------- -------- ----------- -------- --------- delete_test 0 n/a 1 Unspecified 0 n/a delete_update_test 1 Last 1 First 0 n/a

The IsUpdate column has a 1 if the trigger is an update trigger, and it has a 0 otherwise. If the trigger is not an update trigger, the order of the update trigger is n/a. If the trigger is an update trigger, values for the trigger order are First, Last, and Unspecified. You can interpret the IsDelete and DeleteOrd columns in the same way, as well as the IsInsert and InsertOrd columns. This procedure is available on the companion CD.

Rolling Back a Trigger

Executing a ROLLBACK from within a trigger is different from executing a ROLLBACK from within a nested stored procedure. In a nested stored procedure, a ROLLBACK will cause the outermost transaction to abort, but the flow of control continues. However, if a trigger results in a ROLLBACK (because of a fatal error or an explicit ROLLBACK command), the entire batch is aborted.

Suppose the following pseudocode batch is issued from SQL Query Analyzer:

 begin tran delete.... update.... insert.... -- This starts some chain of events that fires a trigger -- that rolls back the current transaction update.... -- Execution never gets to here - entire batch is -- aborted because of the rollback in the trigger if....commit -- Neither this statement nor any of the following -- will be executed else....rollback begin tran.... insert.... if....commit else....rollback GO -- batch terminator only select... -- Next statement that will be executed is here

As you can see, once the trigger in the first INSERT statement aborts the batch, SQL Server not only rolls back the first transaction but skips the second transaction completely and continues execution following the GO.

Misconceptions about triggers include the belief that the trigger cannot do a SELECT statement that returns rows and that it cannot execute a PRINT statement. Although you can use SELECT and PRINT in a trigger, doing these operations is usually a dangerous practice unless you control all the applications that will work with the table that includes the trigger. Otherwise, applications not written to expect a result set or a print message following a change in data might fail because that unexpected behavior occurs anyway. For the most part, you should assume that the trigger will execute invisibly and that if all goes well, users and application programmers will never even know that a trigger was fired.

Be aware that if a trigger modifies data in the same table on which the trigger exists, using the same operation (INSERT, UPDATE, or DELETE) won't, by default, fire that trigger again. That is, if you have an UPDATE trigger for the inventory table that updates the inventory table within the trigger, the trigger will not be fired a second time. You can change this behavior by allowing triggers to be recursive. You control this on a database-by-database basis by setting the option recursive triggers to TRUE. It's up to the developer to control the recursion and make sure that it terminates appropriately. However, it will not cause an infinite loop if the recursion isn't terminated because, just like stored procedures, triggers can be nested only to a maximum level of 32. Even if recursive triggers have not been enabled, if separate triggers exist for INSERT, UPDATE, and DELETE statements, one trigger on a table could cause other triggers on the same table to fire (but only if sp_configure 'nested triggers' is set to 1, as you'll see in a moment).

A trigger can also modify data on some other table. If that other table has a trigger, whether that trigger also fires depends on the current sp_configure value for the nested triggers option. If that option is set to 1 (TRUE), which is the default, triggers will cascade to a maximum chain of 32. If an operation would cause more than 32 triggers to fire, the batch will be aborted and any transaction will be rolled back. This prevents an infinite cycle from occurring. If your operation is hitting the limit of 32 firing triggers, you should probably look at your design—you've reached a point at which there are no longer any simple operations, so you're probably not going to be ecstatic with the performance of your system. If your operation truly is so complex that you need to perform further operations on 32 or more tables to modify any data, you could call stored procedures to perform the actions directly rather than enabling and using cascading triggers. Although cascading triggers can be of value, overusing them can make your system a nightmare to maintain.

Instead-of Triggers

SQL Server 2000 allows you create a second kind of trigger, called an instead-of trigger. An instead-of trigger, rather than the data modification operation that fires the triggers, specifies the action to take. Instead-of triggers are different from after triggers in several ways:

You can have only one instead-of trigger for each action (INSERT, UPDATE, and DELETE).
You cannot combine instead-of triggers and foreign keys that have been defined with CASCADE on a table. For example, if Table2 has a FOREIGN KEY constraint that references Table1 and specifies CASCADE as the response to DELETE operations, you will get an error message if you try to create an instead-of trigger for DELETE on Table2. However, you can have instead-of triggers for INSERT or UPDATE. Similarly, if you already have an instead-of trigger on Table2, you cannot alter the table to add a foreign key constraint with the CASCADE action for the same data modification operation.
Instead-of triggers can never be recursive, regardless of the setting of the recursive triggers database option. For example, if an instead-of trigger is executed for INSERT into Table1 and the trigger does an INSERT into Table1, the instead-of trigger is not processed. Instead, the INSERT is processed as if there were no instead-of trigger for INSERT, and any constraints and after triggers will take effect.

Instead-of triggers are intended to allow updates to views that are not normally updateable. For example, a view that is based on a join normally cannot have DELETE operations executed on it. However, you can write an instead-of DELETE trigger. The trigger has access to the rows of the view that would have been deleted had the view been a real table. The deleted rows are available in a worktable, which is accessed with the name deleted, just like for after triggers. Similarly, in an UPDATE or INSERT instead-of trigger, you can access the new rows in the inserted table.

Here's a simple example that uses a Table1 and Table2 and builds a view on a join of these tables:

 USE pubs SET NOCOUNT ON -- drop table Table1 CREATE TABLE Table1 (a int PRIMARY KEY, b datetime default getdate(), c varchar(10)) --drop table Table2 CREATE TABLE Table2 (a int ,message varchar(100)) GO /* Insert 4 rows into Table1 */ INSERT INTO Table1(a) VALUES (1) INSERT INTO Table1(a) VALUES (2) INSERT INTO Table1(a) VALUES (3) INSERT INTO Table1(a) VALUES (4) /* Insert 6 rows into Table2 */ INSERT INTO Table2 VALUES (1, 'first row') INSERT INTO Table2 VALUES (1, 'second row') INSERT INTO Table2 VALUES (2, 'first row') INSERT INTO Table2 VALUES (2, 'second row') INSERT INTO Table2 VALUES (2, 'third row') INSERT INTO Table2 VALUES (3, 'first row') GO /* Create a view based on a join of the tables and then an INSTEAD-OF TRIGGER on the view */ CREATE VIEW join_view AS SELECT Table1.a as a1, b, c, Table2.a as a2, message FROM Table1 join Table2 ON Table1.a = Table2.a GO CREATE TRIGGER DEL_JOIN ON join_view INSTEAD OF DELETE AS DELETE Table1 WHERE a IN (SELECT a1 FROM deleted) DELETE Table2 WHERE a IN (SELECT a2 FROM deleted)

In this case, the view contained values from each table that could be used to determine which rows in the base table needed to be removed.

In addition to views based on joins, another kind of view that has severe limitations on its updateability is a view based on a UNION. Some UNION views are updateable, and I'll discuss them in Chapter 16 when we look at partitioned views. But for views that don't meet the conditions for partitioning, direct updates might not be possible.

In the following example, I'll create a contacts list view in the pubs database consisting of the name, city, state, and country of all the authors, stores, and publishers:

 USE pubs GO CREATE VIEW contact_list AS SELECT ID = au_id, name = au_fname + ' ' + au_lname, city, state, country = 'USA' FROM authors UNION ALL SELECT stor_id, stor_name, city, state, 'USA' FROM stores UNION ALL SELECT pub_id, pub_name, city, state, country FROM publishers

I want to be able to insert a new contact into this list, and to do that I'll use an instead-of INSERT trigger. The inserted table in the trigger will have values only for the columns included in the view, so all other columns in all three tables will have to have default values or allow NULLS. The only column not meeting this requirement is the contract column of the authors table, which is a bit column. I'll alter the column to give it a default value:

 ALTER TABLE authors ADD CONSTRAINT contract_dflt DEFAULT 0 FOR contract

The trigger must determine which base table should accept the row inserted into the contacts_list view. Author ID values look very different from store or publisher ID values, so by looking for a hyphen, I can determine that the new row is an author. To distinguish stores from publishers, I can notice that there is a CHECK constraint on the underlying publishers table that requires new publisher ID values to start with the digits 99. (You can execute sp_helpconstraint on the publishers table to see this constraint.) This means that I can't allow any stores to have an ID that starts with 99, and if you can't limit yourself to this restriction, you might need a different solution than this instead-of trigger.

The trickiest part of this instead-of trigger is that the view has concatenated the authors' first and last names into a single name column, so the instead-of trigger must separate a single name into a two-part name. It uses the simplest approach of looking for the first space in the name. Everything before the first space is considered the first name, and everything after the first space is considered the last name.

 CREATE TRIGGER Insert_Contact ON contact_list INSTEAD OF INSERT AS IF @@ROWCOUNT = 0 RETURN IF (SELECT COUNT(*) FROM inserted) > 1 BEGIN PRINT 'Only one row at a time can be inserted' RETURN END -- check for a hyphen in the fourth position in the ID IF (SELECT substring(ID,4,1) FROM inserted) = '-' -- Inserting an author INSERT into authors(au_id, au_fname, au_lname, city, state) SELECT id, rtrim(substring(name, 1, charindex(' ',name) - 1)), rtrim(substring(name, charindex(' ',name) + 1, datalength(name) - charindex(' ',name))), city, state FROM inserted ELSE -- Check for two nines at the beginning of the ID IF (SELECT ID FROM inserted) like '99[0-9][0-9]' -- Inserting a publisher INSERT INTO publishers (pub_id, pub_name, city, state, country) SELECT * FROM inserted ELSE -- Inserting a store INSERT INTO stores(stor_id, stor_name, city, state) SELECT id, name, city, state from inserted RETURN

You can write similar instead-of triggers for updates and deletes.

Managing Triggers

The options available for managing triggers are similar to those for managing stored procedures. Here are some of the options:

You can see the definition of a trigger by executing sp_helptext <trigger name>.
You can create a trigger WITH ENCRYPTION so that its text cannot be exposed.
You can change a trigger's definition using ALTER TRIGGER so that it keeps its same internal ID number.

WARNING
If you ALTER a trigger that has had any firing order properties set, all such order values will be lost when the ALTER is done. You must reexecute sp_settriggerorder procedures to reestablish the firing order.

In addition, a special procedure called sp_helptrigger is available. It takes the name of a table as an argument, and optionally the name of a data modification action, and gives you the name and some of the properties for each trigger on that table. However, as I mentioned earlier, sp_helptrigger does not tell you what order the trigger will be fired in, so I created the sp_helptrigger2 procedure to do that.

Using Triggers to Implement Referential Actions

As I discussed in Chapter 6, SQL Server implements FOREIGN KEY (or REFERENCES) constraints. You can define the constraint to either not allow changes that would break the relationship (NO ACTION) or to cascade any changes in the referenced table into the referencing table. The NO ACTION option is the default; with this option specified for your foreign key, if you attempt a data modification that would break a relationship—for example, if you attempt to delete the primary key row while foreign key references exist—the data modification is disallowed and the command is aborted. In other words, no action is taken—in fact, ANSI SQL-92 calls this the NO ACTION referential action. Colloquially, NO ACTION is often referred to as RESTRICT, but technically this is incorrect. ANSI uses RESTRICT for security and permissions purposes.

NOTE
A FOREIGN KEY constraint can of course be directed toward a UNIQUE constraint, not just a PRIMARY KEY constraint. But there is no performance difference if the referenced table is declared using UNIQUE. In this section, we'll refer only to PRIMARY KEY in the referenced table for simplicity.

Keep in mind that a FOREIGN KEY constraint affects two tables, not just one. It affects the referenced table as well as the referencing table. While the constraint is declared on the referencing table, a modification that affects the primary key of the referenced table checks to see whether the constraint has been violated. The change to the referenced table will be either disallowed or cascaded to the referencing table, depending on how the FOREIGN KEY constraint was defined.

As described in Chapter 6, the SQL-92 standard specifies four referential actions for modifying the primary key side of a relationship: NO ACTION, SET NULL, SET DEFAULT, and CASCADE. The four referential actions have the following characteristics. (These descriptions were given in Chapter 6, but I'll repeat them here for your convenience.)

NO ACTION Disallows the action if the FOREIGN KEY constraint would be violated. This is the only referential action implemented by SQL Server 7 for a declared FOREIGN KEY constraint, and it's the only one that must be implemented in order for a product to claim to be "SQL-92 conformant."
SET NULL Updates the referencing table so that the foreign key columns are set to NULL. Obviously, this requires that the columns be defined to allow NULL.
SET DEFAULT Updates the referencing table so that the foreign key columns are set to their DEFAULT values. The columns must have DEFAULT values defined.
CASCADE Updates the referencing table so that the foreign key columns are set to the same values that the primary key was changed to, or deletes the referencing rows entirely if the primary key row is deleted.

A declared FOREIGN KEY constraint, rather than a trigger, is generally the best choice for implementing NO ACTION. The constraint is easy to use and eliminates the possibility of writing a trigger that introduces a bug. In SQL Server 2000, you can also use FOREIGN KEY constraints for the CASCADE action if you want to simply cascade any changes. You can use triggers to implement either of the other referential actions not currently available with declared FOREIGN KEY constraints.

You can also use a trigger to customize the referential actions in other ways. For example, you can customize error messages to make them more informative than the generic messages sent with a violation of a declared constraint. Also, since you cannot use the CASCADE option for FOREIGN KEY constraints that reference the same table, you could use a trigger to implement a cascading action on a self-referencing table.

Recall that constraint violations are tested before triggers fire. If a constraint violation is detected, the statement is aborted and execution never gets to the trigger (and the trigger never fires). Therefore, you cannot use a declared FOREIGN KEY constraint to ensure that a relationship is never violated by the update of a foreign key and then also use a trigger to perform a SET NULL or SET DEFAULT action when the primary key side of the same relationship is changed. If you use triggers to implement SET NULL or SET DEFAULT behavior for changes to the referenced table, you must also use triggers to restrict invalid inserts and updates to the referencing table. You can and should still declare PRIMARY KEY or UNIQUE constraints on the table to be referenced, however. It's relatively easy to write triggers to perform referential actions, as you'll see in the upcoming examples. For the sake of readability, you can still declare the FOREIGN KEY constraint in your CREATE TABLE scripts but then disable the constraint using ALTER TABLE NOCHECK so that it is not enforced. In this way, you ensure that the constraint still appears in the output of sp_help <table> and similar procedures.

NOTE
Although you're about to see triggers that perform referential actions, you might want to consider another option entirely. If you want to define constraints and still have update and delete capability beyond NO ACTION and CASCADE, a good alternative is using stored procedures that exclusively perform the update and delete operations. A stored procedure can easily perform the referential action (within the scope of a transaction) before the constraint would be violated. No violation occurs because the corresponding "fix-up" operation is already performed in the stored procedure. If an INSERT or UPDATE statement is issued directly (and you can prevent that in the first place by not granting such permissions), the FOREIGN KEY constraint still ensures that the relationship is not violated.

Here's an example of using triggers to implement a referential action. Rather than simply implementing NO ACTION, we want to implement ON DELETE SET NULL and ON UPDATE SET DEFAULT for the foreign key relationship between titleauthor and titles. We must create the following three triggers (or suitable alternatives):

 -- 1. INSERT and UPDATE trigger on referencing table. -- Disallow any insert or update if the foreign key title_id -- in the referencing table titleauthor does not match -- the primary key title_id in the referenced table titles. CREATE TRIGGER INS_UPD_titleauthor ON titleauthor FOR INSERT, UPDATE AS -- Do any rows exist in the inserted table that do not have -- a matching ID in titles? IF EXISTS (SELECT * FROM inserted WHERE inserted.title_id NOT IN (SELECT titles.title_id FROM titles) ) BEGIN RAISERROR('No matching title found. Statement will be aborted.', 16, 1) ROLLBACK TRAN END GO -- 2. If primary key in referenced table titles is changed, -- update any rows in titleauthor to the DEFAULT value. -- This implements ON UPDATE SET DEFAULT. Notice that this trigger could -- be easily changed to set the column to NULL instead of DEFAULT, -- which would implement ON UPDATE SET NULL. CREATE TRIGGER UPD_titles ON titles FOR UPDATE AS DECLARE @counter int IF UPDATE(title_id) BEGIN UPDATE titleauthor SET titleauthor.title_id=DEFAULT FROM titleauthor, deleted WHERE titleauthor.title_id=deleted.title_id SET @COUNTER=@@ROWCOUNT -- If the trigger resulted in modifying rows of -- titleauthor, raise an informational message IF (@counter > 0) RAISERROR('%d rows of titleauthor were updated to DEFAULT title_id as a result of an update to titles table', 10, 1, @counter) END GO -- 3. DELETE of the referenced table titles will set to NULL the -- referencing titleauthor rows CREATE TRIGGER DelCascadeTrig ON titles FOR DELETE AS DECLARE @counter int UPDATE titleauthor SET title_id = NULL FROM titleauthor, deleted WHERE titleauthor.title_id=deleted.title_id SET @counter=@@ROWCOUNT IF (@counter > 0) RAISERROR('%d rows of titleauthor were set to a NULL title_id as a result of a delete to the titles table', 10, 1, @counter) GO

NOTE
In order for these examples to run, you must drop or suspend the existing FOREIGN KEY constraints on titleauthor that reference titles as well as FOREIGN KEY constraints on two other tables (sales and roysched) that reference titles. In addition, you must modify the title_id column of titleauthor to allow NULLS, and you must re-create the PRIMARY KEY constraint on the titleauthor table as a UNIQUE constraint so that NULLs in the title_id column are allowed. (The code on the companion CD that corresponds to these examples does this.)

The following trigger is just an example of how you can use triggers to implement a cascade action. This trigger is actually unnecessary because you can define a FOREIGN KEY constraint to cascade any changes. It is a cascading update trigger on the titles (referenced) table that updates all rows in the titleauthor table with matching foreign key values. The cascading update is tricky, which is why I'm presenting it. It requires that you associate both the before and after values (from the inserted and deleted pseudotables) to the referencing table. In a cascading update, you by definition change that primary key or unique value. This means that typically the cascading update works only if one row of the referenced table is updated. If you change the values of multiple primary keys in the same update statement, you lose the ability to correctly associate the referencing table. You can easily restrict an update of a primary key to not affect more than one row by checking the value of the @@ROWCOUNT function, as follows:

 CREATE TRIGGER UpdCascadeTrig1 ON titles AFTER UPDATE AS DECLARE @num_affected int, @title_id varchar(11), @old_title_id varchar(11) SET @num_affected=@@ROWCOUNT IF (@num_affected=0) -- No rows affected, so nothing to do RETURN IF UPDATE(title_id) BEGIN IF (@num_affected=1) BEGIN SELECT @title_id=title_id FROM inserted SELECT @old_title_id=title_id FROM deleted UPDATE titleauthor SET title_id=@title_id FROM titleauthor WHERE titleauthor.title_id=@old_title_id SELECT @num_affected=@@ROWCOUNT RAISERROR ('Cascaded update in titles of Primary Key from %s to %s to %d rows in titleauthor', 10, 1, @old_title_id, @title_id, @num_affected) END ELSE BEGIN RAISERROR ('Cannot update multiple Primary Key values in a single statement due to Cascading Update trigger in existence.', 16, 1) ROLLBACK TRANSACTION END END

Again, this trigger is shown just as an example. If you define a FOREIGN KEY constraint to cascade updates, you can update multiple primary key values in a single update. But you should carefully evaluate whether this is something you really want to do. The inability to update more than one primary key value at a time might save you from making a mistake. You typically won't want to make mass updates to a primary key. If you declare a PRIMARY KEY or UNIQUE constraint (as you should), mass updates usually won't work because of the constraint's need for uniqueness. Although such an update is rare, you might want to set the value to some expression, such as multiplying all Customer ID values by 1000 to renumber them. In a situation like this, the ability to cascade updates can be useful.

Another situation in which you might want to update multiple primary keys in a single statement is if you have a mail-order customer list that uses the customer phone number as the primary key. As phone companies add numbers to accommodate fax machines, server remote access, and cellular phones, some area codes run out of possible numbers, which leads to the creation of new area codes. If a mail-order company needs to keep up with such changes, it must update the primary key for all customers who get new area codes. If you assume that the phone number is stored as a character string of 10 digits, you must update the first three characters only. If the new area code is 425, for example, your UPDATE statement would look something like this:

 UPDATE customer_list SET phone = '425' + substring(phone, 4, 7) WHERE zip IN (list of affected zip codes)

This UPDATE would affect the primary key of multiple rows, and it would be difficult to write a trigger to implement a cascade operation (perhaps into an orders table).

You might think that you can write a trigger to handle multiple updates to the primary key by using two cursors to step through the inserted and deleted tables at the same time. However, this won't work. There's no guarantee that the first row in the deleted table will correspond to the first row in the inserted table, so you cannot determine which old foreign key values in the referencing tables should be updated to which new values.

Although it is logically the correct thing to do, you are not required to create a PRIMARY KEY or UNIQUE constraint on a referenced table. (Unless, of course, you are going to actually declare a FOREIGN KEY constraint to reference a column in the referenced table.) And there is no hard-and-fast requirement that the constraint be unique. (However, not making it unique violates the logical relationship.) In fact, the "one row only" restriction in the previous cascading trigger is a tad more restrictive than necessary. If uniqueness were not required on title_id, the restriction could be eased a bit to allow the update to affect more than one row in the titles table, as long as all affected rows were updated to the same value for title_id. When uniqueness on titles.title_id is required (as should usually be the case), the two restrictions are actually redundant. Since all affected rows are updated to the same value for title_id, you no longer have to worry about associating the new value in the referencing table, titleauthor—there is only one value. The trigger looks like this:

 CREATE TRIGGER UpdCascadeTrig2 ON titles FOR UPDATE AS DECLARE @num_distinct int, @num_affected int, @title_id varchar(11) SET @num_affected=@@ROWCOUNT IF (@num_affected=0) -- No rows affected, so nothing to do RETURN IF UPDATE(title_id) BEGIN SELECT @num_distinct=COUNT(DISTINCT title_id) FROM inserted IF (@num_distinct=1) BEGIN -- Temporarily make it return just one row SET ROWCOUNT 1 SELECT @title_id=title_id FROM inserted SET ROWCOUNT 0 -- Revert ROWCOUNT back UPDATE titleauthor SET titleauthor.title_id=@title_id FROM titleauthor, deleted WHERE titleauthor.title_id=deleted.title_id SELECT @num_affected=@@ROWCOUNT RAISERROR ('Cascaded update of Primary Key to value in titles to %d rows in titleauthor', 10, 1, @title_id, @num_affected) END ELSE BEGIN RAISERROR ('Cannot cascade a multirow update that changes title_id to multiple different values.', 16, 1) ROLLBACK TRANSACTION END END

Using COUNT(DISTINCT title_id) FROM inserted ensures that even if multiple rows are affected, they are all set to the same value. So @title_id can pick up the value of title_id for any row in the inserted table. You use the SET ROWCOUNT 1 statement to limit the subsequent SELECT to only the first row. This is not required, but it's good practice and a small optimization. If you don't use SET ROWCOUNT 1, the assignment still works correctly but every inserted row is selected and you end up with the value of the last row (which, of course, is the same value as any other row). Note that you can't use the TOP clause in a SELECT statement that assigns to a variable, unless the assignment is done by a subquery. For example, you can do this:

 DECLARE @v1 varchar(11) SET @v1 = (SELECT TOP 1 au_id FROM authors) PRINT @v1

but you can't do this:

 DECLARE @v1 varchar(11) SET @v1 = TOP 1 au_id FROM authors PRINT @v1

It's not good practice to do a SELECT or a variable assignment that assumes that just one row is returned unless you're sure that only one row can be returned. You can also ensure a single returned row by doing the assignment like this:

 SELECT @title_id=title_id FROM titles GROUP BY title_id HAVING COUNT(DISTINCT title_id)=1

Because the previous IF allows only one distinct title_id value, you are assured that the preceding SELECT statement returns only one row.

In the previous two triggers, you first determine whether any rows were updated; if none were, you do a RETURN. This illustrates something that might not be obvious: a trigger fires even if no rows were affected by the update. Recall that the plan for the trigger is appended to the rest of the statements' execution plan. You don't know the number of rows affected until execution. This is a feature, not a bug; it allows you to take action when you expect some rows to be affected but none are. Having no rows affected is not an error in this case. However, you can use a trigger to return an error when this occurs. The previous two triggers use RAISERROR to provide either a message that indicates the number of rows cascaded or an error message. If no rows are affected, we don't want any messages to be raised, so we return from the trigger immediately.

Recursive Triggers

SQL Server allows triggers to recursively call themselves if the database option recursive triggers is set to TRUE. This means that if a trigger modifies the table on which the trigger is based, the trigger might fire a second time. But then, of course, the trigger modifies the table and fires the trigger again, which modifies the table and fires the trigger again…and so on. This process does not result in an infinite loop because SQL Server has a maximum nesting depth of 32 levels; after the 32nd call to the trigger, SQL Server generates the following error. The batch stops executing, and all data modifications since the original one that caused the trigger to be fired are rolled back.

 Server: Msg 217, Level 16, State 1, Procedure up_rt1, Line 4 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Writing recursive routines is not for the faint of heart. You must be able to determine what further actions to take based on what has already happened, and you must make sure that the recursion stops before the nesting reaches its limit. Let's look at a simple example. Suppose we have a table that keeps track of budgets for various departments. Departments are members (or children) of other departments, so the budget for a parent department includes the budgets for all its child departments. If a child department's budget increases or decreases, the change must be propagated to the parent department, the parent's parent, and so forth. The following code creates a small budget table and inserts three rows into it:

 CREATE TABLE BUDGET (dept_name varchar(30) not null, parent_name varchar(30) null, budget_amt money not null) GO INSERT INTO budget values ('Internal Training', 'Training', $10) INSERT INTO budget values ('Training', 'Services', $100) INSERT INTO budget values ('Services', NULL, $500) GO

The following trigger is fired if a single row in the budget table is updated. If the department is not the highest-level department, its parent name is not NULL, so you can adjust the budget for the parent department by the same amount that the current row was just updated. (The amount of the change is equal to the new amount in the inserted table minus the old amount in the deleted table.) That new update causes the trigger to fire again. If the next department has a non-null parent, the update is applied again. The recursion stops when you get to the highest level and the parent department is NULL. In that case, the trigger simply returns and all work is done.

 CREATE TRIGGER update_budget ON budget FOR update AS DECLARE @rows int SELECT @rows = @@ROWCOUNT IF (@rows=0) RETURN IF (@rows > 1) BEGIN PRINT 'Only one row can be updated at a time' ROLLBACK TRAN RETURN END IF (SELECT parent_name FROM inserted) IS NULL RETURN UPDATE budget SET budget_amt = budget_amt + (SELECT budget_amt FROM inserted) - (SELECT budget_amt FROM deleted) WHERE dept_name = (SELECT parent_name FROM inserted)