Module 11: Implementing Triggers Overview Introduction Defining - PowerPoint PPT Presentation

Module 11: Implementing Triggers

Overview  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 Triggers

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  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 Modification

Considerations 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 Triggers

Creating Triggers  Requires Appropriate Permissions  Cannot Contain Certain Statements 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  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 (SELECT COUNT(*) FROM Deleted) > 6 BEGIN RAISERROR( 'You cannot delete more than six employees at a time.', 16, 1) ROLLBACK TRANSACTION END  Dropping a Trigger

 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 INSERT statement to a table with an INSERT Trigger Defined TRIGGER Actions Execute INSERT [Order Details] VALUES Trigger Code: (10525, 2, 19.00, 5, 0.2) INSERT Statement to a Table with an INSERT USE Northwind 1 1 CREATE TRIGGER OrdDet_Insert Trigger Defined ON [Order Details] Order Details Order Details FOR INSERT AS OrderID ProductID UnitPrice Quantity Discount 2 2 UPDATE P SET INSERT Statement Logged UPDATE P SET 10522 10 31.00 7 0.2 UnitsInStock = (P.UnitsInStock – I.Quantity) UnitsInStock = (P.UnitsInStock – I.Quantity) FROM Products AS P INNER JOIN Inserted AS I FROM Products AS P INNER JOIN Inserted AS I 10523 41 9.65 9 0.15 3 3 ON P.ProductID = I.ProductID ON P.ProductID = I.ProductID Trigger Actions Executed 10524 7 30.00 24 0.0 Order Details Order Details 10523 2 19.00 5 0.2 Products Products OrderID ProductID UnitPrice Quantity Discount ProductID UnitsInStock … … 10522 10 31.00 7 0.2 1 15 10523 41 9.65 9 0.15 Insert statement logged 2 15 2 10 10524 7 30.00 24 0.0 inserted inserted 3 65 10523 2 19.00 5 0.2 10523 2 19.00 5 0.2 4 20

How a DELETE Trigger Works DELETE Statement to a table with a DELETE Trigger Defined DELETE Statement to a table with a DELETE Trigger Defined Trigger Actions Execute Categories Categories DELETE Statement to a Table with a DELETE CategoryID CategoryName Description Picture 1 1 Products Products DELETE Categories Statement Defined 1 Beverages Soft drinks, coffees… 0x15… WHERE ProductID Discontinued … … 2 Condiments Sweet and savory … 0x15… CategoryID = 4 1 0 3 Confections Desserts, candies, … 0x15… 2 2 DELETE Statement Logged 2 1 2 0 4 Dairy Products Cheeses 0x15… USE Northwind 3 0 CREATE TRIGGER Category_Delete 3 3 Trigger Actions Executed 4 0 ON Categories FOR DELETE AS UPDATE P SET Discontinued = 1 UPDATE P SET Discontinued = 1 FROM Products AS P INNER JOIN deleted AS d FROM Products AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID ON P.CategoryID = d.CategoryID DELETE statement logged Deleted Deleted 4 Dairy Products Cheeses 0x15…

How an UPDATE Trigger Works TRIGGER Actions Execute UPDATE Statement to a table with an UPDATE Trigger Defined USE Northwind UPDATE Employees GO UPDATE Statement to a Table with an UPDATE CREATE TRIGGER Employee_Update SET EmployeeID = 17 1 1 ON Employees WHERE EmployeeID = 2 Trigger Defined FOR UPDATE Employees Employees AS AS IF UPDATE (EmployeeID) IF UPDATE (EmployeeID) EmployeeID LastName FirstName Title HireDate BEGIN TRANSACTION BEGIN TRANSACTION UPDATE Statement Logged as INSERT and 2 2 RAISERROR ('Transaction cannot be processed.\ RAISERROR ('Transaction cannot be processed.\ 1 Davolio Nancy Sales Rep. ~~~ ***** Employee ID number cannot be modified.', 10, 1) ***** Employee ID number cannot be modified.', 10, 1) DELETE Statements 2 Fuller Andrew Vice Pres. ~~~ 2 Barr Andrew R ~~~ ROLLBACK TRANSACTION ROLLBACK TRANSACTION 3 Leverling Janet Sales Rep. ~~~ Transaction cannot be processed. 4 Peacock Margaret Sales Rep. ~~~ 3 3 Trigger Actions Executed ***** Member number cannot be modified Employees Employees UPDATE Statement logged as INSERT and DELETE Statements EmployeeID LastName FirstName Title HireDate inserted inserted 1 Davolio Nancy Sales Rep. ~~~ 17 Fuller Andrew Vice Pres. ~~~ 2 Fuller Andrew Vice Pres. 2 Barr Andrew R ~~~ ~~~ deleted 3 Leverling Janet Sales Rep. ~~~ deleted 4 Peacock Margaret Sales Rep. ~~~ 2 Fuller Andrew Vice Pres. ~~~

How an INSTEAD OF Trigger Works Create a View That Combines Two or More Tables CREATE VIEW UPDATE is Made Customers AS INSTEAD OF Trigger Can Be on a Table or View 1 to the View 1 SELECT * FROM CustomersMex Customers Customers INSTEAD OF UNION CustomerID CompanyName Country Phone … SELECT * trigger directs the 2 The Action That Initiates the Trigger Does NOT Occur 2 FROM CustomersGer ALFKI Alfreds Fu… Germany 030-0074321 ~~~ ALFKI Alfreds Fu… Germany 030-0074321 ~~~ update to the base table ANATR Ana Trujill… Mexico (5) 555-4729 ~~~ ANTON Antonio M… Mexico (5) 555-3932 ~~~ Original Insert to 3 3 Allows Updates to Views Not Previously Updateable CustomersMex the Customers CustomersMex View Does Not CustomerID CompanyName Country Phone … CustomersGer CustomersGer Occur ANATR Ana Trujill… Mexico (5) 555-4729 ~~~ CustomerID CompanyName Country Phone … ANTON Antonio M… Mexico (5) 555-3932 ~~~ ALFKI Alfreds Fu… Germany 030-0074321 ~~~ ALFKI Alfreds Fu… Germany 030-0074321 ~~~ CENTC Centro Co… Mexico (5) 555-3392 ~~~ BLAUS Blauer Se… Germany 0621-08460 ~~~ DRACD Drachenb… Germany 0241-039123 ~~~

How Nested Triggers Work Order_Details Order_Details OrDe_Update OrderID ProductID UnitPrice Quantity Discount 10522 10 31.00 7 0.2 10523 41 9.65 9 0.15 10524 7 30.00 24 0.0 10525 2 19.00 5 0.2 Placing an order causes the OrDe_Update trigger to Products Products execute InStock_Update ProductID UnitsInStock … … Executes an UPDATE 1 15 statement on the Products 2 15 10 table 3 65 4 20 InStock_Update trigger executes UnitsInStock + UnitsOnOrder Sends message is < ReorderLevel for ProductID 2

Recursive Triggers  Activating a Trigger Recursively  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  Determining Whether to Use Recursive Triggers

 Examples of Triggers  Enforcing Data Integrity  Enforcing Business Rules

Enforcing Data Integrity 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 BEGIN DELETE BO FROM BackOrders AS BO INNER JOIN Inserted AS I ON BO.ProductID = I.ProductID END Products BackOrders Products BackOrders ProductID UnitsInStock … … ProductID UnitsOnOrder … 1 15 1 15 Updated 2 15 10 12 10 3 65 3 65 Trigger Deletes Row 4 20 2 15

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 Transaction DELETE statement executed on Trigger code rolled back Product table checks the Order Details table Products Products Order Details Products Products Order Details ProductID UnitsInStock … … ProductID UnitsInStock … … OrderID ProductID UnitPrice Quantity Discount 1 1 15 15 10522 10 31.00 7 0.2 2 0 2 10 10 10523 2 19.00 9 9 0.15 3 3 65 65 10524 41 9.65 24 0.0 4 4 20 20 10525 7 30.00 ' Transaction cannot be processed ' ' This product has order history '