Module 11 Implementing Triggers Overview Introduction Defining Create drop alter triggers How Triggers Work Examples Performance Considerations Analyze performance issues related to triggers Introduction to Triggers What Is a Trigger Uses Considerations for Using Triggers What Is a Trigger Associated with a Table Invoked Automatically Cannot Be Called Directly Is Part of a Transaction Along with the statement that calls the trigger Can ROLLBACK transactions use with care Uses of Triggers Cascade Changes Through Related Tables in a Database A delete or update trigger can cascade changes to related tables Soda name change to change in soda name in Sells table Enforce More Complex Data Integrity Than a CHECK Constraint Change prices in case of price rip offs Define Custom Error Messages Maintain Denormalized Data Automatically update redundant data Compare Before and After States of Data Under Modification Considerations for Using Triggers Triggers Are Reactive Constraints Are Proactive Constraints Are Checked First Tables Can Have Multiple Triggers for Any Action Table Owners Can Designate the First and Last Trigger to Fire You Must Have Permission to Perform All Statements That Define Triggers Table Owners Cannot Create AFTER Triggers on Views or Temporary Tables Defining Triggers Creating Triggers Altering and Dropping Triggers Creating Triggers Requires Appropriate Permissions Cannot Contain Certain Statements Use Use Northwind Northwind GO GO CREATE CREATE TRIGGER TRIGGER Empl Delete Empl Delete ON ON Employees Employees FOR DELETE FOR DELETE AS AS IF IF SELECT SELECT COUNT COUNT FROM FROM Deleted Deleted 11 BEGIN BEGIN RAISERROR RAISERROR You You cannot cannot delete delete more more than than one one employee employee at at aa time time 16 16 1 1 ROLLBACK TRANSACTION ROLLBACK TRANSACTION END END Altering and Dropping Triggers Altering a Trigger Changes the definition without dropping the trigger Can disable or enable a trigger USE USE Northwind Northwind GO GO ALTER ALTER TRIGGER TRIGGER Empl Delete Empl Delete ON ON Employees Employees FOR DELETE FOR DELETE AS AS IF IF SELECT SELECT COUNT COUNT FROM FROM Deleted Deleted 66 BEGIN BEGIN RAISERROR RAISERROR You You cannot cannot delete delete more more than than six six employees employees at at aa time time 16 16 1 1 ROLLBACK TRANSACTION ROLLBACK TRANSACTION END END Dropping a Trigger How Triggers Work How an INSERT Trigger Works How a DELETE Trigger Works How an UPDATE Trigger Works How an INSTEAD OF Trigger Works How Nested Triggers Work Recursive Triggers How an INSERT Trigger Works INSERT statement to a table with an INSERT Trigger Defined TRIGGER Actions Execute INSERT Details VALUES INSERT Order Order Details VALUES Trigger Code Trigger Code 10525 2 5 USE 10525 2 19 00 19 00 5 0 2 0 2 11 USE Northwind Northwind INSERT Statement to a Table with an INSERT CREATE TRIGGER OrdDet Insert CREATE TRIGGER OrdDet Insert ON Order Details Trigger Defined ON Order Details Order Details Order Details FOR INSERT FOR INSERT OrderID AS OrderIDProductID ProductIDUnitPrice UnitPriceQuantity QuantityDiscount Discount AS 22 UPDATE P SET UPDATE P10SET INSERT Statement Logged 10522 31 00 77 0 2 10522 10 31 00 0 2 I Quantity UnitsInStock P UnitsInStock UnitsInStock P UnitsInStock I Quantity FROM Products AS P INNER JOIN Inserted 10523 41 9 65 9 0 15 10523Products 41 9 65 9 0 15 Inserted AS FROM AS P INNER JOIN AS II 33 ON P ProductID I ProductID ON P ProductID I ProductID 10524 77 30 00 24 0 0 10524 30 00 24 0 0 Trigger Actions Executed Order OrderDetails Details 10523 2 OrderID OrderIDProductID ProductIDUnitPrice UnitPriceQuantity QuantityDiscount Discount 10522 31 00 77 10522 10 10 31 00 10523 41 9 65 99 10523 statement 41 9 65 Insert logged 10524 77 30 00 10524inserted 30 00 24 24 inserted 10523 2 19 00 5 10523 2 19 00 10523 2 19 00 0 2 0 2 0 15 0 15 0 0 0 0 0 2 19 00 55 5 Products 0 2 Products ProductID ProductID UnitsInStock UnitsInStock 0 2 0 2 11 22 33 44 15 15 10 15 10 65 65 20 20 How a DELETE Trigger Works DELETE Statement to a table with a DELETE Trigger Defined Trigger Actions Execute Categories Categories CategoryID Description Picture CategoryID CategoryName Description Picture DELETE Statement toCategoryName a Table with a DELETE Products DELETE Categories 11 Beverages Soft DELETE Statement CategoriesDefined Beverages Products Softdrinks drinks coffees coffees 0x15 0x15 WHERE ProductID Discontinued 11 WHERE CategoryID CategoryID 44 Discontinued Condiments Sweet savory Condiments ProductID Sweetand and savory 0x15 0x15 11 00 Confections candies Confections Desserts Desserts candies 0x15 0x15 22 DELETE Logged 222 010 Dairy Products Cheeses 0x15 USE Northwind USE Northwind 33 00 CREATE TRIGGER Category Delete CREATE TRIGGER Category Delete 33 44 00 ON Trigger Actions Executed ON Categories Categories AS AS 22 33 Statement 4 FOR FOR DELETE DELETE UPDATE UPDATE PP SET SET Discontinued Discontinued 11 FROM FROM Products Products AS AS PP INNER INNER JOIN JOIN deleted deleted AS AS dd ON P CategoryID d CategoryID ON DELETE P CategoryID d CategoryID statement logged Deleted Deleted 44 Dairy DairyProducts Products Cheeses Cheeses 0x15 0x15 How an UPDATE Trigger Works TRIGGERStatement Actions Execute UPDATE to a table with an UPDATE Trigger Defined USE Northwind USE Northwind UPDATE Employees GO UPDATE Employees GO SET EmployeeID 17 CREATE TRIGGER Employee Update 11 EmployeeID SET 17 UPDATE Statement to a Table with an UPDATE CREATE TRIGGER Employee Update ON Employees WHERE EmployeeID ON Employees WHERE EmployeeID 22 FOR UPDATE Trigger DefinedEmployees FOR UPDATE AS Employees AS AS IF UPDATE EmployeeID EmployeeID HireDate IF IF UPDATE UPDATE EmployeeID EmployeeID EmployeeID LastName LastName FirstName FirstName Title Title HireDate BEGIN TRANSACTION BEGIN TRANSACTION BEGIN TRANSACTION 22 UPDATE Statement Logged as INSERT andRep RAISERROR Transaction be 1cannot Davolio Nancy RAISERROR Transaction cannot be processed RAISERROR Transaction be processed processed 1cannot Davolio Nancy Sales Sales Rep ID number cannot be modified 10 1 Employee ID number cannot be modified 10 1 Employee Employee ID number cannot be modified 10 1 22 Barr Andrew Fuller Vice DELETE Statements Barr Andrew R R Pres ROLLBACK TRANSACTION ROLLBACK TRANSACTION ROLLBACK TRANSACTION 33 Leverling Leverling Janet Janet Sales SalesRep Rep Peacock Margaret Sales Rep Transaction cannot be processed 44 Peacock Margaret Sales Rep 33 Trigger Executed Member numberActions cannot