Assertions and TriggersRose-Hulman Institute of TechnologyCurt CliftonAssertions Like constraints: Recall: state IN {'IA', 'MN', 'WI', 'MI', 'IL'} But can reference all tables Defined by: CREATE ASSERTION <name>CHECK ( <condition> );Example: Assertion In Sells(rest, soda, price), no rest may chargean average of more than $3. CREATE ASSERTION NoRipoffs CHECK (NOT EXISTS (SELECT rest FROM SellsGROUP BY restHAVING AVG(price) > 3));Example: Assertion The minimum price charged for productsmade by Coca-Cola Co. is $2 Recall: Soda(name, manf) Sells(rest, soda, price)Example: Assertion The minimum price charged for products made byCoca-Cola Co. is $2 CREATE ASSERTION NoCheapCokeCHECK(NOT EXISTS(SELECT * FROM Sells, SodaWHERE Sells.soda = Soda.nameAND Soda.manf = 'Coca-Cola Co.'AND Sells.price < 2.00))Timing of Assertion Checks Logically, assertions always are true So when do we have to check them?Timing of Assertion Checks Logically, assertions always are true So when do we have to check them? Logically, after any change Practically, the DBMS could calculate the set ofimportant changesTriggers: Motivation All the power of assertions But easier to implement: Column- and row-based checks Programmer specifies when they are activated Most DBMS just include triggers, not assertionsWhat 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 ina Database Enforce More Complex Data Integrity Than aCHECK Constraint Define Custom Error Messages Automatically update redundant data Compare Before and After States of DataUnder ModificationCreating Triggers Requires Appropriate Permissions Cannot Contain Certain Statements: e.g., DROP DATABASEUse 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 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 TRANSACTIONENDAltering and Dropping Triggers Altering a Trigger DISABLE TRIGGER Empl_Delete ON Employees ENABLE TRIGGER Empl_Delete ON Employees DROP TRIGGER Empl_DeleteHow 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 Works Consider:USE NorthwindCREATE TRIGGER OrdDet_InsertON [Order Details]FOR INSERTASUPDATE P SETUnitsInStock = (P.UnitsInStock – I.Quantity)FROM Products AS P INNER JOIN Inserted AS ION P.ProductID = I.ProductIDINSERT [Order Details] VALUES(10523, 2, 19.00, 5, 0.2)Order DetailsOrder DetailsOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7924Discount0.20.150.0 5 19.002 0.210523Insert statement loggedinsertedinserted10523 2 19.00 5 0.2ProductsProductsProductID UnitsInStock … …1234151065202 5How an INSERT Trigger WorksHow a DELETE Trigger Works Consider:USE NorthwindCREATE TRIGGER Category_DeleteON CategoriesFOR DELETEASUPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deletedAS dON P.CategoryID = d.CategoryIDDeletedDeleted4Dairy ProductsCheeses 0x15…DELETE statement loggedCategoriesCategoriesCategoryID123CategoryNameBeveragesCondimentsConfectionsDescriptionSoft drinks, coffees…Sweet and savory …Desserts, candies, …Picture0x15…0x15…0x15… 0x15…CheesesDairy Products4DELETE CategoriesWHERECategoryID = 4ProductsProductsProductID Discontinued … CategoryID1234000014232 1 4How a DELETE Trigger WorksHow an UPDATE Trigger Works Consider:USE NorthwindGOCREATE TRIGGER Employee_UpdateON EmployeesFOR UPDATEASIF UPDATE (EmployeeID)BEGINRAISERROR ('Transaction cannot be processed.\***** Employee ID number cannot be modified.',10, 1)ROLLBACK TRANSACTIONENDUPDATE EmployeesSET EmployeeID = 17WHERE EmployeeID = 2EmployeesEmployeesEmployeeID LastName FirstName Title HireDate1234DavolioBarrLeverlingPeacockNancyAndrewJanetMargaretSales Rep.RSales Rep.Sales Rep.~~~~~~~~~~~~2 Fuller Andrew Vice Pres.~~~UPDATE Statement logged as INSERT and DELETE Statementsinsertedinserted17 Fuller Andrew Vice Pres. ~~~deleteddeleted2 Fuller Andrew Vice Pres. ~~~ Transaction cannot be processed. ***** Member number cannot be modifiedEmployeesEmployeesEmployeeID LastName FirstName Title HireDate1234DavolioBarrLeverlingPeacockNancyAndrewJanetMargaretSales Rep.RSales Rep.Sales Rep.~~~~~~~~~~~~2 Fuller Andrew Vice Pres.~~~How an UPDATE Trigger WorksINSTEAD OF Triggers INSTEAD OF trigger lets us interpret viewmodifications that wouldn’t be allowed Example view: CREATE VIEW Synergy(cust,soda,rest)ASSELECT Likes.customer, Sells.soda, Sells.restFROM Likes, Sells, FrequentsWHERE Likes.customer = Frequents.customerAND Sells.soda = Likes.sodaAND Sells.rest = Frequents.restInterpreting a View Insertion INSERT INTO Synergy(cust, soda, rest)VALUES ('Molly', 'Sunkist', 'Regal Beagle') What does that mean? Can use INSTEAD OF trigger to decideThe Trigger CREATE TRIGGER SynergyInsert ON SynergyINSTEAD OF INSERTASDECLARE @c nvarchar(30)DECLARE @s nvarchar(30)DECLARE @r nvarchar(30)SELECT @c=cust, @s=soda, @r=restFrom InsertedINSERT INTO Likes VALUES(@c, @s)INSERT INTO Frequents VALUES(@c, @r)INSERT INTO Sells VALUES(@r, @s, null)INSTEAD OF Triggers Can use them on views to define action Can also use them on regular tables Optionally perform or ignore actionsHow Nested Triggers WorkUnitsInStock + UnitsOnOrder is < ReorderLevel for ProductID 2OrDe_UpdatePlacing an order causes theOrDe_Update trigger toexecuteExecutes an UPDATEstatement on the ProductstableInStock_UpdateProductsProductsProductID UnitsInStock … …134151065202 15 InStock_Update triggerexecutesSends messageOrder_DetailsOrder_DetailsOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7924Discount0.20.150.0 10525 19.002 0.25Recursive Triggers Activating a Trigger Recursively See ALTER DATABASE command Types of Recursive Triggers Direct recursion occurs when a trigger fires and performsan action that causes the same