891
Chapter 36: Creating Triggers
36
Analytics & Guessing
';Result:------------------------------------------
Analytics & Guessing
changed to
Guessing & PowerPoints(1 row(s) affected)The following AFTER trigger in the Family sample database enforces a rule that the
FatherID points to a valid person (covered by the foreign key)and who also must be male:CREATE TRIGGER HumanResources.Empolyee_OrgNode
ON HumanResources.Employee
AFTER INSERT, UPDATE
AS
IF UPDATE(OrganizationNode)
BEGIN;
-- Incorrect Father Gender
IF EXISTS(
SELECT *
FROM HumanResources.Employee emp
INNER JOIN Inserted
ON Inserted.OrganizationNode = emp.OrganizationNode
WHERE emp.OrganizationLevel = 0x)
BEGIN;
ROLLBACK;
RAISERROR('Incorrect OrganizationNode for Chief Executive
Officer',14,1);
RETURN;
END;
END;Multiple-Trigger Interaction
Multiple triggers can become disorganized and extremely diffi cult to troubleshoot unless
they are carefully planned and their effects understood.Trigger Organization
It is recommended that triggers be organized not by table event, but by the trigger’s task,
including the following:■ (^) Data validation
■ (^) Complex business rules
c36.indd 891c36.indd 891 7/31/2012 10:03:08 AM7/31/2012 10:03:08 AM
http://www.it-ebooks.info