Trigger Specifics - Instead-of Triggers
(Page 3 of 4 )
Instead-of triggers are executed instead of the modification statement that has initiated them. The following trigger is executed when an attempt is made to delete records from the MyEquipment table. It will report an error instead of allowing the deletion:
Create Trigger dbo.itrMyEquipment_D
On dbo.MyEquipment
instead of Delete
As
-- deletion in this table is not allowed
raiserror('Deletion of records in MyEquipment
table is not allowed', 16, 1)
GO
Instead-of triggers are executed after changes to base tables occur in Inserted and Deleted virtual tables, but before any change to the base tables is executed. Therefore, the trigger can use information in the Inserted and Deleted tables. In the following example, a trigger tests whether some of the records that would have been deleted are in use in the Equipment table:
Create Trigger itrEqType_D
On dbo.EqType
instead of Delete
As
If exists(select *
from Equipment
where EqTypeId in (select EqTypeId
from deleted)
)
raiserror('Some recs in EqType are in use in Equipment table!',
16, 1)
else
delete EqType
where EqTypeId in (select EqTypeId from deleted)
GO
Instead-of triggers are initiated before any constraints. This behavior is very different from that of After triggers. Therefore, the code for an Instead-of trigger must perform all checking and processing that would normally be performed by constraints.
Usually, an Instead-of trigger executes the modification statement (Insert, Update, or Delete) that initiates it. The modification statement does not initiate the trigger again. If some After triggers and/or constraints are defined on the table or view, they will be executed as though the Instead-of trigger does not exist.
A table or a view can have only one Instead-of trigger (and more than one After trigger) per modification type.
Next: Triggers on Views >>
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.
|
|
