[u]SQL Server 2000's INSTEAD OF Triggers</u>
Restrictions
Anything good usually comes with some restrictions, and INSTEAD OF triggers are no exception. While you may have more than one FOR (now AFTER) trigger per triggering action, you may have only one INSTEAD OF trigger per triggering action on a given object. You can fake this, however, by creating a stack of views with each view being a SELECT * on the next view until the final view is a SELECT * of the base object. You then attach the INSTEAD OF triggers on each of the views, and all access is done through the topmost view.
There are also restrictions with respect to the use of INSTEAD OF triggers where cascaded DELETEs or UPDATEs have been put in place through DRI (declarative referential integrity). You can't have an INSTEAD OF trigger on a table with the corresponding CASCADE action, so there can be no INSTEAD OF DELETE when ON DELETE CASCADE is in effect. In other words, if you've created a table that has a FOREIGN KEY constraint with ON DELETE CASCADE, you can't create an INSTEAD OF DELETE trigger on that same table. The same restriction applies to INSTEAD OF UPDATE triggers and cascaded UPDATEs. If you attempt to create the trigger when the CASCADE option is in effect, the CREATE TRIGGER statement will fail. If you create the trigger first and then alter the table to add the FOREIGN KEY constraint with the CASCADE option, the ALTER TABLE statement will fail.
Inside SQL Server - INSTEAD OF Triggers
Trigger Limitations
Hope that helps
Cheers!
_________________________
- Vijay G
Strive for Perfection
Triggers with views
Strive for Perfection