Brief Introduction to Triggers in SQL Server 2000 - Mixing Trigger Types
(Page 4 of 4 )
If you were to define an Instead Of trigger and an After trigger on the same table for the same operation, what would happen?
Because an After trigger fires after an operation completes, and an 'instead of' trigger prevents the operation from taking place, the After trigger would never fire in this situation.
However, if an Instead Of trigger on a (say) delete operation contains a subsequent delete on the same table, then any After trigger defined for the delete operation on that table will fire on the basis of the delete statement issued from the Instead Of trigger. The original delete statement is not executed, only the Delete in the Instead Of trigger runs.
This code sample creates a trigger of each type, and changed the nature of the delete statement issued so that only comics that have a value of 0 in the preserve column can be deleted.
CREATE TABLE Gupta (Comic VARCHAR (32), Preserve INT)
GO
INSERT Gupta
SELECT 'groucho', 1 UNION
SELECT 'chico', 1 UNION
SELECT 'harpo', 0 UNION
SELECT 'zeppo', 0
GO
CREATE TRIGGER trGuptaDelete ON Gupta
FOR DELETE
AS
SELECT Comic AS "deleting_these_names_only"
FROM deleted
GO
CREATE TRIGGER tr_Gupta_InsteadOf ON Gupta
INSTEAD OF DELETE
AS
DELETE Gupta
FROM Gupta
INNER JOIN Deleted
ON Gupta.Comic = Deleted.Comic
WHERE Gupta.Preserve= 0
GO
DELETE Gupta WHERE Comic IN ('GROUCHO', 'HARPO')
GO
SELECT * FROM Gupta
DROP TABLE Gupta
Important
Triggers can be used in scenarios such as if the database is de-normalized and requires an automated way to update redundant data contained in multiple tables, or if customized messages and complex error handling are required, or if a value in one table must be validated against a non-identical value in another table.
Triggers are a powerful tool that can be used to enforce the business rules automatically when the data is modified. Triggers can also be used to maintain the data integrity. But they are not to maintain data integrity. Triggers should be used to maintain the data integrity only if you are unable to enforce the data integrity using CONSTRAINTS, RULES and DEFAULTS. Triggers cannot be created on the temporary tables.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 93 
