Microsoft SQL Server 2008 R2 Unleashed- P102:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 954 CHAPTER 30 Creating and Managing Triggers trigger and inclusion of the word AFTER in the name of the old trigger have helped accentuate 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 modification 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 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 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 GO UPDATE authors SET aufname aufname Download .
