By: Tim Smith | Updated: 2019-01-10 | Comments (2) | Related: > Triggers
Problem
Our developers use triggers on some of our SQL Server tables to track specific DML operations. While these can be useful for auditing, they do create issues sometimes with blocking or slowing down other transactions. We have worked with the developers to reduce using table triggers, but some of them will remain because they are appropriate for auditing and we'd like a convenient script that tracks all triggers in a database with a script that we can use for either disabling them or to be aware of the table triggers, if we determine that they may cause an issue.
Solution
SQL Server triggers can be useful in some development contexts, but they can cause issues in some of the following situations:
- They can cause delays during transactions or deployments when they track the events of those transactions or deployments and the tracking table is tied up by other resources, such as another DML operation.
- They add OLTP overhead as they introduce more write transactions and their target may be used in reports, inviting more reads and possible conflict.
- In large loads, they may introduce significant overhead relative to what events are tracked (such as tracking inserts during a massive insert load).
- Developers can sometimes forget to scale the trigger target table when scaling the tracked table: for an example, three scaled tables with triggers that all add tracking data to the same target table, which quickly becomes the bottleneck.
- They are very easy to forget, especially in urgent situations.
Triggers can increase or create OLTP events, which invite blocking or delay due to their automatic nature. The positive is that they are automatic, which may be a design we want in some contexts.
In this tip, we'll look at a way we can reduce the likelihood of these errors by creating a report of these triggers for our database with the option of disabling them from a script on the report. We should be careful about immediately disabling a trigger since we may want it to fire for validation or auditing - for an example, we might want to capture a data change during a deployment or during a set of transactions. The flip side to this is that we may have a huge bulk load that we don't want to track, as it's a one-time load, and thus we want to disable the trigger prior to loading data.
Query to Find All Enabled Triggers in a Database
First, we'll look at getting trigger information from T-SQL.
In the below query, which we'll be using for our report, we're looking at this from the perspective of triggers on tables by the name of the table that possesses the trigger. We want to filter out all disabled triggers, Microsoft internal triggers, and DML-based triggers only. This gives us active DML triggers that have been developed where we may see issues during data changes. Relative to our naming conventions, this may be enough to indicate whether we should disable triggers or not - for instance, we may disable all triggers involved in an ETL flow while allowing others and if our naming convention demarcates these objects by name, we can start here. Also, both objects sys.triggers and sys.tables are also available for querying in AzureSQL databases along with SQL Server databases, so we can apply this query in both contexts.
SELECT t2.[name] TableTriggerReference , SCHEMA_NAME(t2.[schema_id]) TableSchemaName , t1.[name] TriggerName FROM sys.triggers t1 INNER JOIN sys.tables t2 ON t2.object_id = t1.parent_id WHERE t1.is_disabled = 0 AND t1.is_ms_shipped = 0 AND t1.parent_class = 1
Query to Find All Enabled Triggers in a Database Including Row Counts
Another alternative is to use the row counts of tables as an indication where a trigger may be a problem. This may be true in many situations where a large table receives a significant volume during a load and it's possible we want the trigger disabled prior to the load. Keep in mind that we may have a trigger on a large table that is an archive table of data that should seldom have new data (thus the trigger audits additions, making it a trigger that we do not want to disable). Between getting the row count and the name (if we've named our objects to indicate their use), we may have a better idea for our next step. The object sys.sysindexes is not available in AzureSQL, but is an option we can use in SQL Server. If a load is currently processing, the result for rowcnt may only indicate the latest update, and this is something we should consider.
SELECT t2.[name] TableTriggerReference , SCHEMA_NAME(t2.[schema_id]) TableSchemaName , t3.[rowcnt] TableReferenceRowCount , t1.[name] TriggerName FROM sys.triggers t1 INNER JOIN sys.tables t2 ON t2.object_id = t1.parent_id INNER JOIN sys.sysindexes t3 On t2.object_id = t3.id WHERE t1.is_disabled = 0 AND t1.is_ms_shipped = 0 AND t1.parent_class = 1
Query to Find All Enabled Triggers in a Database Including Script Code to Disable
Finally, for our report we'll add a script that we can quickly use for disabling the triggers on our report.
We should be careful about disabling a trigger as the situation may be appropriate. For example, if we're updating metadata for our application during a deployment and that metadata must be tracked, but we disable the trigger, our metadata history won't show what we've done - we'll have to look through our deployment history. This may be appropriate for our purposes and we want to ensure that our situation calls for this solution, otherwise we've made more work for ourselves.
SELECT t2.[name] TableTriggerReference , SCHEMA_NAME(t2.[schema_id]) TableSchemaName , t3.[rowcnt] TableReferenceRowCount , t1.[name] TriggerName , 'ALTER TABLE ' + SCHEMA_NAME(t2.schema_id) + '.' + t2.[name] + ' DISABLE TRIGGER ' + t1.[name] Script FROM sys.triggers t1 INNER JOIN sys.tables t2 ON t2.object_id = t1.parent_id INNER JOIN sys.sysindexes t3 On t2.object_id = t3.id WHERE t1.is_disabled = 0 AND t1.is_ms_shipped = 0 AND t1.parent_class = 1
It's worth noting on this final report that naming convention can assist us, if it's appropriate for our environment (some security contexts may restrict this). It may be a standard runbook instruction that cautions new DBAs or support staff who can execute these scripts to only disable triggers involved in ETL (for some situations) and if these objects are identifiable in their naming, this becomes a faster task given this report. Naming also allows us to automate this, where we look through every table and disable triggers if the name of the table or trigger indicate when we may want to disable it (such as load_, etl_, etc.).
Since we may have contexts in which we want to use triggers, we can use these queries to track the triggers we have on a database and output this information quickly with a script that allows us to disable them, if necessary. We may only have a few triggers we want to disable, or we may not need to disable any of them. With this script, we will have more insight relative to what we need before situations which requires us to make a quick decision.
The Script column provides the T-SQL command to disable the trigger. We can just copy this code, paste in a query window and execute to disable the trigger.
Query to Find All Disabled Triggers in a Database Including Script Code to Enable
If we need to find all triggers that have been disabled, we can run the following. The Script column provides the code we can use to enable a trigger that has been disabled, similar to what was done above.
SELECT t2.[name] TableTriggerReference , SCHEMA_NAME(t2.[schema_id]) TableSchemaName , t3.[rowcnt] TableReferenceRowCount , t1.[name] TriggerName , 'ALTER TABLE ' + SCHEMA_NAME(t2.schema_id) + '.' + t2.[name] + ' ENABLE TRIGGER ' + t1.[name] Script FROM sys.triggers t1 INNER JOIN sys.tables t2 ON t2.object_id = t1.parent_id INNER JOIN sys.sysindexes t3 On t2.object_id = t3.id WHERE t1.is_disabled = 1 AND t1.is_ms_shipped = 0 AND t1.parent_class = 1
Next Steps
- Triggers have an alternative design of "governors" - a queue or flow table that holds transactions which are then processed which inverts the trigger process - the "governor" table tells the application what to do next and is used for audits. This does allow for ease of tracking, since the design is custom and we tend to have more awareness with something we've created, but the costs of developing the governor method is much higher.
- Triggers can add complexity to an environment and cause headaches in the wrong situation. This doesn't mean that they're inappropriate in all contexts, as they can be an appropriate design relative to what we need. We should use them with caution while considering where we've created them and where they may introduce problems, if applicable. The above report will also help us quickly identify what table triggers may be an issue on the database level using referenced table names and row counts, assuming that a trigger is the underlying cause.
- We can use the output of these queries to create a report or dashboard that we can use prior to events where triggers may give us headaches, like large loads, deployments, heavy transactions, etc. as an alternative to automatically disabling triggers if the automatic solution is not something useful.
- With the above output, you can see how descriptively naming your triggers can quickly give you an idea of what the trigger does. This will aid in making decisions to determine which triggers you need to further review.
- Check out these additional resources:
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-01-10
