Module 11: Implementing TriggersOverviewIntroduction to TriggersWhat Is a Trigger?Uses of TriggersConsiderations for Using TriggersDefining TriggersCreating TriggersAltering and Dropping TriggersHow Triggers WorkHow an INSERT Trigger WorksHow a DELETE Trigger WorksHow an UPDATE Trigger WorksHow an INSTEAD OF Trigger WorksHow Nested Triggers WorkRecursive TriggersExamples of TriggersEnforcing Data IntegrityEnforcing Business RulesPerformance ConsiderationsRecommended PracticesReviewModule 11: Implementing TriggersOverview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 TriggersWhat 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 ModificationConsiderations 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 TriggersCreating TriggersRequires Appropriate PermissionsCannot Contain Certain Statements Use NorthwindGOCREATE TRIGGER Empl_Delete ON EmployeesFOR DELETE ASIF (SELECT COUNT(*) FROM Deleted) > 1BEGIN RAISERROR( 'You cannot delete more than one employee at a time.', 16, 1) ROLLBACK TRANSACTIONENDUse NorthwindGOCREATE TRIGGER Empl_Delete ON EmployeesFOR DELETE ASIF (SELECT COUNT(*) FROM Deleted) > 1BEGIN RAISERROR( 'You cannot delete more than one employee at a time.', 16, 1) ROLLBACK TRANSACTIONENDAltering and Dropping TriggersAltering a TriggerChanges the definition without dropping the triggerCan disable or enable a triggerDropping a TriggerUSE NorthwindGOALTER TRIGGER Empl_Delete ON EmployeesFOR DELETE ASIF (SELECT COUNT(*) FROM Deleted) > 6BEGIN RAISERROR( 'You cannot delete more than six employees at a time.', 16, 1) ROLLBACK TRANSACTIONENDUSE NorthwindGOALTER TRIGGER Empl_Delete ON EmployeesFOR DELETE ASIF (SELECT COUNT(*) FROM Deleted) > 6BEGIN RAISERROR( 'You cannot delete more than six employees at a time.', 16, 1) ROLLBACK TRANSACTIONEND 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 TriggersHow an INSERT Trigger WorksINSERT statement to a table with an INSERT Trigger DefinedINSERT [Order Details] VALUES(10525, 2, 19.00, 5, 0.2)INSERT [Order Details] VALUES(10525, 2, 19.00, 5, 0.2)Order DetailsOrder DetailsOrder DetailsOrder DetailsOrderIDOrderID105221052310524105221052310524ProductIDProductID1041710417UnitPriceUnitPrice31.009.6530.0031.009.6530.00QuantityQuantity79247924DiscountDiscount0.20.150.00.20.150.0 5 19.002 0.210523Insert statement loggedinsertedinsertedinsertedinserted10523105232219.0019.00550.20.2TRIGGER Actions ExecuteOrder DetailsOrder DetailsOrder DetailsOrder DetailsOrderIDOrderID105221052310524105221052310524ProductIDProductID1041710417UnitPriceUnitPrice31.009.6530.0031.009.6530.00QuantityQuantity79247924DiscountDiscount0.20.150.00.20.150.0 5 19.002 0.210523Trigger Code:USE NorthwindCREATE TRIGGER OrdDet_InsertON [Order Details]FOR INSERTASUPDATE P SET UnitsInStock = (P.UnitsInStock – I.Quantity)FROM Products AS P INNER JOIN Inserted AS ION P.ProductID = I.ProductIDTrigger Code:USE NorthwindCREATE TRIGGER OrdDet_InsertON [Order Details]FOR INSERTASUPDATE P SET UnitsInStock = (P.UnitsInStock – I.Quantity)FROM Products AS P INNER JOIN Inserted AS ION P.ProductID = I.ProductIDUPDATE P SET UnitsInStock = (P.UnitsInStock – I.Quantity)FROM Products AS P INNER JOIN Inserted AS ION P.ProductID = I.ProductIDProductsProductsProductsProductsProductIDProductIDUnitsInStockUnitsInStock…………1234123415106520151065202 15INSERT Statement to a Table with an INSERTTrigger DefinedINSERT Statement LoggedTrigger Actions Executed111122223333How a DELETE Trigger WorksDELETE Statement to a table with a DELETE Trigger DefinedDELETE Statement to a table with a DELETE Trigger DefinedDeletedDeletedDeletedDeleted44Dairy ProductsDairy ProductsCheesesCheeses0x15…0x15…DELETE statement loggedCategoriesCategoriesCategoriesCategoriesCategoryIDCategoryID123123CategoryNameCategoryNameBeveragesCondimentsConfectionsBeveragesCondimentsConfectionsDescriptionDescriptionSoft drinks, coffees…Sweet and savory …Desserts, candies, …Soft drinks, coffees…Sweet and savory …Desserts, candies, …PicturePicture0x15…0x15…0x15…0x15…0x15…0x15… 0x15…CheesesDairy Products4DELETE CategoriesWHERE CategoryID = 4DELETE CategoriesWHERE CategoryID = 4USE NorthwindCREATE TRIGGER Category_DeleteON CategoriesFOR DELETEASUPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryIDUSE NorthwindCREATE TRIGGER Category_DeleteON CategoriesFOR DELETEASUPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryIDProductsProductsProductsProductsProductIDProductIDDiscontinuedDiscontinued…………1234123400000000Trigger Actions Execute2 1UPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryIDDELETE Statement to a Table with a DELETEStatement DefinedDELETE Statement LoggedTrigger Actions Executed111122223333How an UPDATE Trigger WorksUPDATE Statement to a table with an UPDATE Trigger DefinedUPDATE EmployeesSET EmployeeID = 17WHERE EmployeeID = 2UPDATE EmployeesSET EmployeeID = 17WHERE EmployeeID = 2UPDATE Statement logged as INSERT and DELETE