Module 15 Responding to Data Manipulation via Triggers

1. Module 15 Responding to Data Manipulation via Triggers

2. Module Overview • Designing DML Triggers • Implementing DML Triggers • Advanced Trigger Concepts

3. Lesson 1: Designing DML Triggers • What are DML Triggers? • AFTER Triggers vs. INSTEAD OF Triggers • inserted and deleted Virtual Tables • SET NOCOUNT ON • Trigger Performance Considerations

4. What are DML Triggers? Triggers are special stored procedures that execute when events occur. DML triggers fire on INSERT, UPDATE, DELETE. Other types of triggers: DDL triggers fire on statements like CREATE, ALTER, DROP ü Logon triggers fire when a session is established ü

5. AFTER Triggers vs. INSTEAD OF Triggers • AFTER triggers • Fire after the event that they relate to • Are treated as part of the same transaction as the statement that triggered them • Can roll back the statement that triggered them (and any transaction that statement was part of) • INSTEAD OF triggers • Allow executing alternate code – unlike a BEFORE trigger in other database engines • Are often used to create updatable views with more than one base table • Both can be implemented in managed code or T-SQL

6. Inserted and Deleted Virtual Tables • inserted and deleted Virtual Tables • Allow us to access the state of the data before and after the modification began • Virtual tables are often joined to the modified table data • Available in both AFTER and INSTEAD OF triggers

7. SET NOCOUNT ON Triggers should not return rows of data ü Client applications often check the number of rows affected by data modification statements ü Triggers should generally not change that count ü SET NOCOUNT ON avoids affecting outer statement ü The configuration setting ‘disallow results from triggers’ can be used to prevent triggers from returning resultsets.

8. Trigger Performance Considerations Constraints are preferred to triggers ü Triggers are complex to debug ü Constraints avoid data modification overhead on violation ü Triggers use a rowversion store in tempdb database ü Excess trigger usage can impact tempdb performance ü Triggers can increase the duration of transactions ü

9. Lesson 2: Implementing DML Triggers • AFTER INSERT Triggers • Demonstration 2A: AFTER INSERT Triggers • AFTER DELETE Triggers • Demonstration 2B: AFTER DELETE Triggers • AFTER UPDATE Triggers • Demonstration 2C: AFTER UPDATE Triggers

10. AFTER INSERT Triggers • INSERT statement is executed • AFTER INSERT trigger then fires • Ensure multi-row INSERTs are supported CREATETRIGGERTR_Opportunity_Insert ONSales.Opportunity AFTERINSERTAS BEGIN SETNOCOUNTON; INSERTINTOSales.OpportunityAudit (OpportunityID,ActionPerformed,ActionOccurredAt) SELECTi.OpportunityID, 'I', SYSDATETIME() FROMinserted AS i; END;

11. Demonstration 2A: AFTER INSERT Triggers In this demonstration you will see how to: • Create an AFTER INSERT trigger • Test the trigger action • Drop the trigger

12. AFTER DELETE Triggers • DELETE statement is executed • AFTER DELETE trigger then fires CREATETRIGGERTR_Category_Delete ONProduct.Category AFTERDELETEAS BEGIN SETNOCOUNTON; UPDATEp SETp.Discontinued= 1 FROMProduct.ProductAS p INNERJOIN deleted as d ONp.CategoryID=d.CategoryID; END; GO

13. Demonstration 2B: AFTER DELETE Triggers In this demonstration you will see how to: • Create an AFTER DELETE trigger • Test the trigger • Drop the trigger

14. AFTER UPDATE Triggers • UPDATE statement is executed • AFTER UPDATE trigger then fires CREATETRIGGERTR_ProductReview_Update ONProduct.ProductReview AFTERUPDATEAS BEGIN SETNOCOUNTON; UPDATEpr SETProduct.ProductReview.ModifiedDate=SYSDATETIME() FROMProduct.ProductReviewASpr INNERJOIN inserted AS i ONi.ProductReviewID=pr.ProductReviewID; END;

15. Demonstration 2C: AFTER UPDATE Triggers In this demonstration, you will see how to: • Create an AFTER UPDATE trigger • Test the trigger • Query the sys.triggers view

16. Lesson 3: Advanced Trigger Concepts • INSTEAD OF Triggers • Demonstration 3A: INSTEAD OF Triggers • How Nested Triggers Work • Considerations for Recursive Triggers • UPDATE Function • Trigger Firing Order • Alternatives to Using Triggers • Demonstration 3B: Replacing Triggers with Computed Columns

17. INSTEAD OF Triggers • INSERT, UPDATE, or DELETE statement requested to be executed • Statement does not execute • INSTEAD OF trigger code executes instead CREATETRIGGERTR_ProductReview_Delete ONProduct.ProductReview INSTEADOFDELETEAS BEGIN SETNOCOUNTON; UPDATEprSETpr.Discontinued= 1 FROMProduct.ProductReviewASpr INNERJOIN deleted as d ONpr.ProductReviewID=d.ProductReviewID; END;

18. Demonstration 3A: INSTEAD OF Triggers In this demonstration, you will see how to: • Create an INSTEAD OF DELETE trigger • Test the trigger

19. How Nested Triggers Work INSERT, UPDATE, or DELETE statement 1 3 …and so on… 2 Trigger executes INSERT, UPDATE, or DELETE on another table…

20. Considerations for Recursive Triggers Disabled by default – to enable: • ALTER DATABASE AdventureWorks2008R2SET RECURSIVE_TRIGGERS ON Considerations: • Careful design and thorough testing to ensure that the 32 level nesting limit is not exceeded • Can be difficult to control the order of table updates • Can usually be replaced with non-recursive logic • Option only affects direct recursion

21. UPDATE Function • UPDATE determines if a particular column is being updated • Used in triggers AFTER INSERT or AFTER UPDATE CREATETRIGGERTR_Product_Update_ListPriceAudit ONProduction.Product AFTERUPDATEAS BEGIN IFUPDATE(ListPrice) BEGIN INSERTINTOProduction.ListPriceAudit (ProductID,ListPrice,ChangedWhen) SELECTi.ProductID, i.ListPrice, SYSDATETIME() FROM inserted AS i; END; END;

22. Trigger Firing Order • Multiple triggers may be created for a single event • You cannot specify the order that the triggers will fire • sp_settriggerorder allows you to specify which triggers will fire first and last EXECsp_settriggerorder @triggername= 'Production.TR_Product_Update_ListPriceAudit', @order='First', @stmttype='UPDATE';

23. Alternatives to Using Triggers Many developers use triggers in situations where other alternatives would be preferable • Use constraints for checking values • Use defaults for values not supplied during INSERTs • Use foreign key constraints to check for referential integrity • Use computed and persisted computed columns • Use indexed views for pre-calculating aggregates

24. Demonstration 3B: Replacing Triggers with Computed Columns In this demonstration you will see how a trigger could be replaced by a computed column.

25. Lab 15: Responding to Data Manipulation via Triggers • Exercise 1: Create the Audit Trigger • Challenge Exercise 2: Improve the Audit Trigger (Only if time permits) Logon information Estimated time: 45minutes

26. Lab Scenario The Marketing.CampaignBalance table holds details of amounts of money still available for each of the marketing campaigns the company is undertaking. Changes to the balances are considered quite sensitive. You are required to audit any changes to data in the table. You have decided to implement this via DML triggers as the requirements in this case are not provided for directly by the SQL Server Audit mechanism.

27. Lab Review • What advantages does the use of triggers for auditing provide over other options? • What did you need to specify as well as the trigger’s name when altering it?

28. Module Review and Takeaways • Review Questions • Best Practices