Brief Introduction to Triggers in SQL Server 2000 - Multiple After and Instead Of Triggers
(Page 3 of 4 )
Multiple After Triggers
More than one trigger can now be defined on a table for each Insert/Update/Delete. Although in general you might not want to do this (it's easy to get confused if you over-use triggers) there are situations where this is ideal. One example that springs to mind is that you can split your triggers up into two categories:
- Application based triggers (cascading deletes or validation for example)
- Auditing triggers (for recording details of changes to critical data)
This would allow you to alter triggers of one type without fear of accidentally breaking the other.
If you are using multiple triggers, it is of course essential to know which order they fire in. A new stored procedure called sp_settriggerorder allows you to set a trigger to be either the "first" or "last" to fire.
If you want more than two triggers to fire in a specific order, there is no way to specifically define this. A deeply unscientific test I did indicated that multiple triggers for the same table and operation will run in the order they were created unless you specifically tell them otherwise. I would not recommend relying on this though.
Instead Of Triggers
Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, and they will not actually get deleted (unless you issue another delete instruction from within the trigger) as in this simple example:
CREATE TABLE Mayank (Name varchar(32))
GO
CREATE TRIGGER tr_mayank ON Mayank
INSTEAD OF DELETE
AS
PRINT 'Sorry - you cannot delete this data'
GO
INSERT Mayank
SELECT 'Cannot' union
SELECT 'Delete' union
SELECT 'Me'
GO
DELETE Mayank
GO
SELECT * FROM Mayank
GO
DROP TABLE Mayank
If you were to print out the contents of the Inserted and Deleted tables from inside an Instead Of trigger you would see they behave in exactly the same way as normal. In this case the Deleted table holds the rows you were trying to delete, even though they will not get deleted.
Instead of Triggers can be used in some very powerful ways!
- You can define an Instead Of trigger on a view (something that will not work with After triggers) and this is the basis of the Distributed Partitioned Views that are used so split data across a cluster of SQL Servers.
- You can use Instead Of triggers to simplify the process of updating multiple tables for application developers.
Next: Mixing Trigger Types >>
More MS SQL Server Articles
More By Mayank Gupta
/ 91 
