AFTER Triggers

AFTER Triggers

An AFTER trigger is the original mechanism that SQL Server created to provide an automated response to data modifications. Prior to the release of SQL Server 2000, the AFTER trigger was the only type of trigger, and the word AFTER was rarely used in its name . Any trigger written for prior versions of SQL Server or documentation referring to these triggers is for AFTER triggers.

SQL Server 2000 has a new type of trigger called an INSTEAD OF trigger. This trigger will be discussed in a later section in this chapter (see the later section titled "INSTEAD OF Triggers"). The introduction of this new trigger and the inclusion of the word AFTER in the name of the old trigger has helped accentuate the behavior of the AFTER trigger ”namely the fact that the AFTER trigger executes after a data modification has taken place.

NOTE

For the rest of this chapter, if the trigger type is not specified, you can assume that it is a reference to an AFTER trigger.

The fact that an AFTER trigger fires after a data modification might seem like a simple concept, but it is critical to understanding how it works. What this means is that 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 ability to roll back its actions as well as the actions of the modification statement that invoked it. This is possible because an implicit transaction exists that includes both the modification statement and the trigger it fires. If the trigger does not issue a rollback, then an implicit COMMIT of all the work is issued when the trigger completes.

Now take a look at a simple example before delving deeper into the features of the AFTER trigger:

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 trigger, so the AFTER keyword is optional.

In Listing 29.1, you will create a trigger in the Pubs database that prints a message stating the number of rows updated by an UPDATE statement. You will then execute a couple of UPDATE statements to see whether the trigger works.

Listing 29.1 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 WHERE state = 'UT' GO TRIGGER OUTPUT: 2 rows were updated. UPDATE authors SET au_fname = au_fname WHERE state = 'CA' GO TRIGGER OUTPUT: 15 rows were updated. 

Even though you did not actually change the contents of the au_fname column (setting it to itself), the trigger fired anyway. This is not a 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 the @@rowcount is equal to zero, then the trigger can return without executing the remainder of the trigger code. This is a good tactic for optimizing the performance of your triggers.

NOTE

Triggers are meant to guarantee the integrity of data. Although you can return resultsets and messages in triggers, it is not recommended. The programmers who write applications that perform modifications on your table are probably not prepared to get resultsets 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.

Execution

You know that the AFTER trigger fires when a data modification (such as INSERT , UPDATE , or DELETE ) takes place. What about the trigger's execution in relation to other events, including the execution of constraints? The following list shows those events that take place before an AFTER trigger executes:

• Constraint processing ”This includes check constraints, unique constraints, and primary key constraints.

• Declarative referential actions ”These are the actions defined by Foreign Key constraints that ensure the proper relationships between tables.

• Triggering action ”This is the data modification that 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 your triggers. For example, if you have a constraint and a trigger defined on the same column, any violations to the constraint will abort the statement and the trigger execution won't occur.

Firing Order

With SQL Server 2000 and SQL Server 7.0, you can specify more than one trigger for each data modification action. In other words, you can have multiple triggers responding to an INSERT , UPDATE , or DELETE . This can be useful in certain situations, but can also generate confusion because you might not know the order in which these triggers fire for the particular action.

SQL Server 2000 has alleviated some of the confusion by allowing you to specify the first and last trigger that fires 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 will remain unknown.

The sp_settriggerorder procedure is the tool that you use to set the order. This procedure has the trigger name, order value ( FIRST , LAST , or NONE ), and action ( INSERT , UPDATE , or DELETE ) as parameters. For example, the following syntax can be used 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 will set 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.

NOTE

It is recommended that you avoid defining multiple triggers for the same event on the same table when possible. Oftentimes, it is possible to include all of the logic in one trigger defined for the action. This can simplify your database and avoid the uncertainty of the firing order.

Special Considerations

Following are a few other points that are good to know about AFTER triggers:

• AFTER triggers can be used on tables that also have cascading referential integrity constraints. The cascading feature, which is new to SQL Server 2000, allows you to define cascading actions when a user updates or deletes a key to which a foreign key points. This new feature is discussed in more detail in Chapter 12, "Creating and Managing Tables in SQL Server."

• 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.

• Triggers are objects, so they must have unique names within the database. If you try to add a trigger that already exists, an error message is returned. You can, however, ALTER an existing trigger.

AFTER Trigger Restrictions

The following restrictions apply to AFTER triggers:

• AFTER triggers can be placed only on tables, not on views.

• An AFTER trigger cannot be placed on more than one table.

• The text , ntext and image columns cannot be referenced in the AFTER trigger logic.