Module 10: Implementing Triggers

1. Module 10:Implementing Triggers

2. Overview • Introducing Triggers • Creating, Altering, and Dropping Triggers • Working with Triggers • Implementing Triggers

3. Lesson: Introducing Triggers • SQL Server Triggers • What Are Triggers Used For? • Sequence of Events and Restrictions

4. SQL Server Triggers • Associated with a Table • Invoked Automatically • Cannot Be Called Directly • Is Part of a Transaction • Can include a ROLLBACK TRANSACTION statement • If a ROLLBACK TRANSACTION statement is encountered, the entire transaction rolls back.

5. What Are Triggers Used For? • Cascade Changes Through Related Tables • Enforce More Complex Data Integrity • Define Custom Error Messages • Maintain Denormalized Data • Compare Before and After States of DataUnder Modification

6. Sequence of Events and Restrictions • 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

7. Lesson: Creating, Altering, and Dropping Triggers • The CREATE TRIGGER Statement • The ALTER TRIGGER Statement • The DROP TRIGGER Statement

8. The CREATE TRIGGER Statement • Requires Appropriate Permissions • Cannot Contain Certain Statements USE Northwind GO CREATE TRIGGER Empl_Delete ON Employees FOR DELETE AS IF @@ROWCOUNT > 1 BEGIN RAISERROR( 'You cannot delete more than one employee at a time.', 16, 1) ROLLBACK TRANSACTION END

9. The ALTER TRIGGER Statement Altering a Trigger • Changes the definition without dropping the trigger • Can disable or enable a trigger USE Northwind GO ALTER TRIGGER Empl_Delete ON Employees FOR DELETE AS IF @@ROWCOUNT > 6 BEGIN RAISERROR( 'You cannot delete more than six employees at a time.', 16, 1) ROLLBACK TRANSACTION END

10. The DROP TRIGGER Statement • Dropping a Trigger • Dropped automatically when its associated tableis dropped • Information is removed from the sysobjectsand syscomments • Required Permission • The table owner • Members of sysadmin • Members of db_owner USE Northwind GO DROP TRIGGER Empl_Delete GO

11. Lesson: Working with Triggers • How INSERT Triggers Work • How DELETE Triggers Work • How UPDATE Triggers Work • How INSTEAD OF Triggers Work • How Nested Triggers Work • Recursive Triggers

12. INSERT Statement to a Table with an INSERT Trigger Defined Trigger Code: 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 1 INSERT Statement Logged 2 Trigger Actions Executed 3 How INSERT Triggers Work INSERT statement to a table with an INSERT Trigger Defined • TRIGGER Actions Execute INSERT [Order Details] VALUES (10525, 2, 19.00, 5, 0.2) Insert statement logged

13. DELETE Statement to a Table with a DELETE Statement Defined 1 DELETE Statement Logged 2 Trigger Actions Executed 3 How DELETE Triggers Work DELETE Statement to a table with a DELETE Trigger Defined Trigger Actions Execute 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 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 DELETE Categories WHERE CategoryID = 4

14. UPDATE Statement to a Table with an UPDATE Trigger Defined 1 UPDATE Statement Logged as INSERT and DELETE Statements 2 Trigger Actions Executed 3 How UPDATE Triggers Work UPDATE Statement to a table with an UPDATE Trigger Defined TRIGGER Actions Execute USE Northwind GO CREATE TRIGGER Customer_Update ON Customers FOR UPDATE AS IF UPDATE (CustomerID) BEGIN RAISERROR ('Transaction cannot be processed.\ ***** Customer ID cannot be modified.', 10, 1) ROLLBACK TRANSACTION END UPDATE Customers SET CustomerID = ‘AHORN’ WHERE CustomerID = ‘AROUT’ UPDATE Statement logged as INSERT and DELETE Statements Transaction cannot be processed. ***** Customer ID cannot be modified

15. How INSTEAD OF Triggers Work Create a View That Combines Two or More Tables CREATE VIEW Customers AS SELECT * FROM CustomersMexUNION SELECT * FROM CustomersGer • UPDATE is Made to the View • INSTEAD OF trigger directs the update to the base table • Original Insert to the Customers View Does Not Occur INSTEAD OF Trigger Can Be on a Table or View 1 The Action That Initiates the Trigger Does NOT Occur 2 Allows Updates to Views Not Previously Updateable 3

16. How Nested Triggers Work Order_Insert Placing an order causes the Order_Insert trigger to execute InStock_Update Executes an UPDATE statement on the Products table InStock_Update trigger executes UnitsInStock + UnitsOnOrder is < ReorderLevel for ProductID 2 Sends message

17. Recursive Triggers • Activating a Trigger Recursively • Types of Recursive Triggers • Direct recursion • Indirect recursion • Determining Whether to Use Recursive Triggers

18. Lesson: Implementing Triggers • Performance Considerations • Best Practices • Example: Auditing with Triggers • Example: Enforcing Business Rules

19. Performance Considerations • Triggers Work Quickly Because the Inserted and Deleted Tables Are in Cache • Execution Time Is Determined by: • Number of tables that are referenced • Number of rows that are affected • Actions Contained in Triggers Implicitly Are Partof a Transaction

20. Best Practices Use Triggers Only When Necessary ü Keep Trigger Definition Statements as Simpleas Possible ü Include Recursion Termination Check Statements in Recursive Trigger Definitions ü Minimize Use of ROLLBACK Statementsin Triggers ü

21. Example: Auditing with Triggers UPDATE Employees SET Salary=40000 WHERE EmployeeId = 2 Updated CREATE TRIGGER Salary_Audit ON Employees FOR UPDATE AS… Trigger Inserts Row

22. Example: Enforcing Business Rules Products with Outstanding Orders Cannot Be Deleted IF (Select Count (*) FROM [Order Details] INNER JOIN deleted ON [Order Details].ProductID = deleted.ProductID ) > 0 ROLLBACK TRANSACTION DELETE statement executed on Product table Trigger code checks the Order Details table 'Transaction cannot be processed' 'This product has order history' Transaction rolled back

23. Lab A: Creating Triggers • Exercise 1: Creating Triggers • Exercise 2: Creating a Trigger for Updating Derived Data • Exercise 3: Creating a Trigger That Maintains a Complex Business Rule • Exercise 4: Testing the Firing Order of Constraints and Triggers