Assertions and Triggers Rose Hulman Institute of Technology Curt Clifton Assertions 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 charge an average of more than 3 CREATE ASSERTION NoRipoffs CHECK NOT EXISTS SELECT rest FROM Sells GROUP BY rest HAVING AVG price 3 Example Assertion The minimum price charged for products made by Coca Cola Co is 2 Recall Soda name manf Sells rest soda price Example Assertion The minimum price charged for products made by Coca Cola Co is 2 CREATE ASSERTION NoCheapCoke CHECK NOT EXISTS SELECT FROM Sells Soda WHERE Sells soda Soda name AND 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 of important changes Triggers 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 assertions 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 Enforce More Complex Data Integrity Than a CHECK Constraint Define Custom Error Messages Automatically update redundant data Compare Before and After States of Data Under Modification Creating Triggers Requires Appropriate Permissions Cannot Contain Certain Statements e g DROP DATABASE Use Northwind GO CREATE TRIGGER Empl Delete ON Employees FOR DELETE AS IF SELECT COUNT FROM Deleted 1 BEGIN RAISERROR You cannot delete more than one employee at a time 16 1 ROLLBACK TRANSACTION END Altering and Dropping Triggers Altering a Trigger USE Northwind GO ALTER TRIGGER Empl Delete ON Employees FOR DELETE AS IF SELECT COUNT FROM Deleted 6 BEGIN RAISERROR You cannot delete more than six employees at a time 16 1 ROLLBACK TRANSACTION END DISABLE TRIGGER Empl Delete ON Employees ENABLE TRIGGER Empl Delete ON Employees DROP TRIGGER Empl Delete 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 Consider USE Northwind CREATE TRIGGER OrdDet Insert ON Order Details FOR INSERT AS UPDATE P SET UnitsInStock P UnitsInStock I Quantity FROM Products AS P INNER JOIN Inserted AS I ON P ProductID I ProductID How an INSERT Trigger Works INSERT Order Details VALUES 10523 2 19 00 5 0 2 Order Details OrderID ProductID UnitPrice Quantity Discount 10522 10523 10524 10523 10 41 7 2 31 00 9 65 30 00 19 00 7 0 2 9 0 15 24 0 0 5 Products 0 2 ProductID UnitsInStock Insert statement logged inserted 10523 2 19 00 5 0 2 1 2 3 4 15 5 10 65 20 How a DELETE Trigger Works Consider USE Northwind CREATE TRIGGER Category Delete ON Categories FOR DELETE AS UPDATE P SET Discontinued 1 FROM Products AS P INNER JOIN deleted AS d ON P CategoryID d CategoryID How a DELETE Trigger Works Categories DELETE Categories WHERE CategoryID 4 CategoryID CategoryName 1 2 3 4 Description Beverages Soft drinks coffees Condiments Sweet and savory Confections Desserts candies Dairy Products Cheeses Products Picture 0x15 0x15 0x15 0x15 ProductID Discontinued CategoryID 1 22 3 4 DELETE statement logged Deleted Dairy Products Cheeses 4 0x15 0 10 0 0 1 4 2 3 How an UPDATE Trigger Works Consider USE Northwind GO CREATE TRIGGER Employee Update ON Employees FOR UPDATE AS IF UPDATE EmployeeID BEGIN RAISERROR Transaction cannot be processed Employee ID number cannot be modified 10 1 ROLLBACK TRANSACTION END How an UPDATE Trigger Works UPDATE Employees SET EmployeeID 17 WHERE EmployeeID 2 Employees EmployeeID LastName FirstName 1 2 3 Transaction cannot be processed 4 Member number cannot be modified Title Davolio Nancy Sales Rep Fuller Andrew Vice Barr R Pres Leverling Janet Sales Rep Peacock Margaret Sales Rep HireDate Employees UPDATE Statement logged as INSERT and DELETE Statements HireDate inserted EmployeeID LastName FirstName Title Nancy Sales Rep 17 Fuller 1AndrewDavolio Vice Pres 2 Fuller Andrew Vice Barr R Pres deleted 3 Leverling Janet Sales Rep 2 Fuller 4AndrewPeacock Vice Pres Sales Rep Margaret INSTEAD OF Triggers INSTEAD OF trigger lets us interpret view modifications that wouldn t be allowed Example view CREATE VIEW Synergy cust soda rest AS SELECT Likes customer Sells soda Sells rest FROM Likes Sells Frequents WHERE Likes customer Frequents customer AND Sells soda Likes soda AND Sells rest Frequents rest Interpreting a View Insertion INSERT INTO Synergy cust soda rest VALUES Molly Sunkist Regal Beagle What does that mean Can use INSTEAD OF trigger to decide The Trigger CREATE TRIGGER SynergyInsert ON Synergy INSTEAD OF INSERT AS DECLARE c nvarchar 30 DECLARE s nvarchar 30 DECLARE r nvarchar 30 SELECT c cust s soda r rest From Inserted INSERT 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 actions How Nested Triggers Work Order Details OrDe Update OrderID ProductID UnitPrice Quantity Discount 10522 10523 10524 10525 InStock Update 10 41 7 2 31 00 9 65 30 00 19 00 7 9 24 5 Products ProductID UnitsInStock 1 2 3 4 15 15 10 65 20 0 2 0 15 0 0 0 2 Placing an order causes the OrDe Update trigger to execute Executes an UPDATE statement on the Products table InStock Update trigger executes UnitsInStock UnitsOnOrder is ReorderLevel for ProductID 2 Sends message Recursive Triggers Activating a Trigger Recursively See ALTER DATABASE command Types of Recursive Triggers Direct recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again Indirect recursion occurs when a trigger fires and performs an action that causes a trigger on another table to fire that causes the original trigger to fire again Examples of Triggers Enforcing Data Integrity Enforcing Business Rules CREATE TRIGGER BackOrderList Delete ON Products FOR UPDATE AS IF SELECT BO ProductID FROM BackOrders AS BO JOIN Inserted AS I ON BO ProductID I Product ID 0