module 15 responding to data manipulation via triggers
DESCRIPTION
Module 15 Responding to Data Manipulation via Triggers. Module Overview. Designing DML Triggers Implementing DML Triggers Advanced Trigger Concepts. Lesson 1: Designing DML Triggers. What are DML Triggers? AFTER Triggers vs. INSTEAD OF Triggers i nserted and deleted Virtual Tables - PowerPoint PPT PresentationTRANSCRIPT
Module 15Responding to Data
Manipulation via Triggers
Module Overview• Designing DML Triggers• Implementing DML Triggers• Advanced Trigger Concepts
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
What are DML Triggers?
Other types of triggers:
DDL triggers fire on statements like CREATE, ALTER, DROP
Logon triggers fire when a session is established
ü
ü
Triggers are special stored procedures that execute when events occur. DML triggers fire on INSERT, UPDATE, DELETE.
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
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
Statement inserted deletedINSERT rows just insertedDELETE rows just deletedUPDATE modified row contents original row contents
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.
Trigger Performance Considerations
Constraints are preferred to triggersü
Constraints avoid data modification overhead on violationü
Triggers use a rowversion store in tempdb databaseü
Excess trigger usage can impact tempdb performanceü
Triggers are complex to debugü
Triggers can increase the duration of transactionsü
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
AFTER INSERT Triggers
CREATE TRIGGER TR_Opportunity_InsertON Sales.OpportunityAFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO Sales.OpportunityAudit (OpportunityID, ActionPerformed, ActionOccurredAt) SELECT i.OpportunityID, 'I', SYSDATETIME() FROM inserted AS i;END;
• INSERT statement is executed• AFTER INSERT trigger then fires• Ensure multi-row INSERTs are supported
Demonstration 2A: AFTER INSERT TriggersIn this demonstration you will see how to:• Create an AFTER INSERT trigger• Test the trigger action• Drop the trigger
AFTER DELETE Triggers
CREATE TRIGGER TR_Category_Delete ON Product.CategoryAFTER DELETE AS BEGIN SET NOCOUNT ON; UPDATE p SET p.Discontinued = 1 FROM Product.Product AS p INNER JOIN deleted as d ON p.CategoryID = d.CategoryID;END;GO
• DELETE statement is executed• AFTER DELETE trigger then fires
Demonstration 2B: AFTER DELETE TriggersIn this demonstration you will see how to:• Create an AFTER DELETE trigger• Test the trigger• Drop the trigger
AFTER UPDATE Triggers
CREATE TRIGGER TR_ProductReview_UpdateON Product.ProductReviewAFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE pr SET Product.ProductReview.ModifiedDate = SYSDATETIME() FROM Product.ProductReview AS pr INNER JOIN inserted AS i ON i.ProductReviewID = pr.ProductReviewID;END;
• UPDATE statement is executed• AFTER UPDATE trigger then fires
Demonstration 2C: AFTER UPDATE TriggersIn this demonstration, you will see how to:• Create an AFTER UPDATE trigger• Test the trigger• Query the sys.triggers view
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
INSTEAD OF Triggers
CREATE TRIGGER TR_ProductReview_Delete ON Product.ProductReviewINSTEAD OF DELETE AS BEGIN SET NOCOUNT ON; UPDATE pr SET pr.Discontinued = 1 FROM Product.ProductReview AS pr INNER JOIN deleted as d ON pr.ProductReviewID = d.ProductReviewID;END;
• INSERT, UPDATE, or DELETE statement requested to be executed• Statement does not execute• INSTEAD OF trigger code executes instead
Demonstration 3A: INSTEAD OF TriggersIn this demonstration, you will see how to:• Create an INSTEAD OF DELETE trigger• Test the trigger
How Nested Triggers Work
INSERT, UPDATE, or DELETE statement
Trigger executes INSERT, UPDATE, or DELETE on another table…
1
23…and so on…
Considerations for Recursive Triggers
Disabled by default – to enable:
• ALTER DATABASE AdventureWorks2008R2SET RECURSIVE_TRIGGERS ON
• Can usually be replaced with non-recursive logic
• 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
Considerations:
• Option only affects direct recursion
UPDATE Function
CREATE TRIGGER TR_Product_Update_ListPriceAuditON Production.ProductAFTER UPDATE AS BEGIN IF UPDATE(ListPrice) BEGIN INSERT INTO Production.ListPriceAudit (ProductID, ListPrice, ChangedWhen) SELECT i.ProductID, i.ListPrice, SYSDATETIME() FROM inserted AS i; END;END;
• UPDATE determines if a particular column is being updated• Used in triggers AFTER INSERT or AFTER UPDATE
Trigger Firing Order
EXEC sp_settriggerorder @triggername= 'Production.TR_Product_Update_ListPriceAudit', @order='First', @stmttype = 'UPDATE';
• 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
Alternatives to Using TriggersMany 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
Demonstration 3B: Replacing Triggers with Computed ColumnsIn this demonstration you will see how a trigger could be replaced by a computed column.
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: 45 minutes
Virtual machine 623XB-MIA-SQLUser name AdventureWorks\AdministratorPassword Pa$$w0rd
Lab ScenarioThe 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.
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?
Module Review and Takeaways• Review Questions• Best Practices