Triggers - What Triggers a Trigger?
(Page 3 of 4 )
A DML trigger is executed once for each modification statement (Insert, Update, or Delete). An After trigger is fired after the modification statement finishes successfully. If a statement fails for another reason (for example, foreign key or Check constraints), the trigger is not invoked. For example, the Equipment table has the following Delete trigger:
Alter Trigger Equipment_DeleteTrigger
On dbo.Equipment
After Delete -- For Delete
As
Print 'One or more rows are deleted in Equipment table!'
If you attempt to delete all records from the table:
delete dbo.Equipment
SQL Server aborts the execution because there is a foreign key relationship with the Inventory table. The execution is aborted before the trigger is invoked:
Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_Inventory_EqId". The conflict occurred in database "Asset5", table "Inventory", column 'EqId'.
The statement has been terminated.
A trigger and developer might have different definitions of what is a successfully finished modification to a table. The trigger will fire even when a modification statement affected zero records. The following example is based on the assumption that the record with EqId set to 77777 does not exist in the database:
Delete dbo.Equipment
Where EqId = 77777
SQL Server nonchalantly prints from the trigger:
One or more rows are deleted in Equipment table!
Next: Full Syntax of After Triggers >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 8 
