1 / 23

Module 10: Implementing Triggers

Module 10: Implementing Triggers. Overview. Introducing Triggers Creating, Altering, and Dropping Triggers Working with Triggers Implementing Triggers. Lesson: Introducing Triggers. SQL Server Triggers What Are Triggers Used For? Sequence of Events and Restrictions. SQL Server Triggers.

freddiep
Download Presentation

Module 10: Implementing Triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related