Keeping track of trigger executions
One important factor when monitoring triggers is to have a history of their execution. This allows you to associate the timings between trigger runs and any issues that occur in the database.
If the times match, it's possible that the problems were caused by the trigger.
SQL Server provides information about the execution of any triggers that are currently stored in memory in the sys.dm_exec_trigger_stats view. But once a trigger is removed from the memory, any information about it is removed from the view as well, so you lose the trigger execution history.
To overcome this limitation, you decide to make use of the TriggerAudit table to store information about any attempts to modify rows in the Orders table, because people have reported the table is often unresponsive.
This is a part of the course
“Building and Optimizing Triggers in SQL Server”
Exercise instructions
- Modify the
PreventOrdersUpdatetrigger. - Set the trigger to fire when rows are updated in the
Orderstable. - Add additional details about the trigger execution into the
TriggerAudittable.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Modify the trigger to add new functionality
___ TRIGGER PreventOrdersUpdate
ON ___
-- Prevent any row changes
INSTEAD OF ___
AS
-- Keep history of trigger executions
INSERT INTO ___ (TriggerName, ExecutionDate)
SELECT 'PreventOrdersUpdate',
GETDATE();
RAISERROR ('Updates on "Orders" table are not permitted.
Place a new order to add new products.', 16, 1);This exercise is part of the course
Building and Optimizing Triggers in SQL Server
Learn how to design and implement triggers in SQL Server using real-world examples.
Learn to delete and modify triggers. Acquaint yourself with the way trigger management is done. Learn how to investigate problematic triggers in practice.
Exercise 1: Deleting and altering triggersExercise 2: Removing unwanted triggersExercise 3: Modifying a trigger's definitionExercise 4: Disabling a triggerExercise 5: Re-enabling a disabled triggerExercise 6: Trigger managementExercise 7: Managing existing triggersExercise 8: Counting the AFTER triggersExercise 9: Troubleshooting triggersExercise 10: Keeping track of trigger executionsExercise 11: Identifying problematic triggersExercise 12: Wrapping upWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.