Troubleshooting triggers
1. Troubleshooting triggers
In this lesson, you will learn how to troubleshoot and optimize triggers. You will learn how to keep a history of trigger runs, and how to search for triggers causing issues.2. Tracking trigger executions (system view)
One important factor when troubleshooting triggers is to have a history of their execution. This allows you to associate the timing of trigger runs with issues caused by triggers. SQL Server provides information on the execution of triggers that are currently stored in memory. The information can be seen in "sys.dm_exec_trigger_stats". Unfortunately, when a trigger is removed from the memory, the information about that trigger is removed from the view as well. This leaves you with a major problem regarding tracking trigger execution history. There is no other option to see the history of trigger runs, unless you create a custom solution.3. Tracking trigger executions (custom solution)
For example, suppose we want to create a permanent record of executions of the trigger "PreventOrdersUpdate", designed to prevent any updates to the "Orders" table.4. Tracking trigger executions (custom solution)
We can use the "TriggerAudit" table to store information on trigger execution. To enable this, we will update the trigger definition to specify that when it executes, the trigger name and the current date and time will be inserted into the "TriggerAudit" table. We'll enhance the trigger definition with the new code using the ALTER statement, as shown here.5. Tracking trigger executions (custom solution)
An attempt to update the quantity for the order with number 784 will result in the "PreventOrdersUpdate" trigger being fired. The trigger will throw an error message saying that updates are not permitted on the "Orders" table.6. Tracking trigger executions (custom solution)
However, with our change to the trigger definition in place, it will also insert information about the trigger execution into the "TriggerAudit" table. If we check the contents of that table, we will see the information about this trigger execution. We now have a permanent record that we can use to track the history of trigger runs. A solution like this will allow you to associate the timings of trigger executions with issues potentially caused by the trigger in future investigations. If the date and time of the issue are similar to the date and time when the trigger was executed, you can assume the trigger might be causing the problem and investigate further.7. Identifying triggers attached to a table
Sometimes you may have issues with certain tables and suspect triggers to be the root cause. But how can you investigate them if no documentation is available? In this example, we want to find out what triggers are attached to the `Products` table. Starting from the table name, we can query the "sys.objects" view to find the table ID of "Products". We can then use this ID to determine what triggers are attached to the table, and get some information about them.8. Identifying triggers attached to a table
We enhance the script further by joining the first query with the "sys.triggers" view. This will help us to find the triggers attached to the "Products" table. The join is made by matching the "parent_id" column of the trigger with the "object_id" column of the "Products" table. We add the following information to our script: the trigger name (from the "name" column), whether it's disabled (the "is_disabled" column), and whether it's an INSTEAD OF trigger (the "is_instead_of_trigger" column). Columns with the "o" prefix are coming from "sys.objects" and columns with the "t" prefix are coming from "sys.triggers".9. Identifying triggers attached to a table
The end result is starting to look better, as we now have some insights on the existing triggers attached to the "Products" table.10. Identifying events capable of firing a trigger
Now we'll add one more important column with another INNER JOIN on "sys.trigger_events".11. Identifying events capable of firing a trigger
The addition of the "type_desc" column brings us details on the events capable of firing the triggers.12. Viewing the trigger definitions
The last important information to add is the trigger definitions. To troubleshoot a trigger's results or behavior, you need to know what it's intended to do. To view the trigger definition code, we will make use of the OBJECT_DEFINITION function. This function will return the definition for an object ID passed as an argument. In this particular example, it will be the ID of the trigger.13. Viewing the trigger definitions
The trigger definitions can now be inspected and modified if needed.14. Let's practice!
There was a lot of interesting stuff in this lesson, right? You can now practice with all these new concepts!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.