By: Aaron Bertrand | Comments (4) | Related: > Triggers
Problem
There are a few scenarios (such as re-initializing replication) where users need to perform an action on all triggers in a database – disable, enable, or script out for source control. Is there a quick way to do this that is easier than pointing and clicking through the Object Explorer tree in SQL Server Management Studio (SSMS)?
Solution
Many tasks that you can accomplish with a menu or command in SSMS, you can also automate using the catalog views and a bit of dynamic SQL. I will state up front that if what you are simply trying to copy all triggers from one database to another, dbatools (Copy-DbaServerTrigger) is what you’re after.
Let’s build a temporary table to hold our trigger objects, including the database name, in case we want to handle multiple databases at once.
CREATE TABLE #triggers
(
[database] sysname,
[schema] sysname,
[object] sysname,
name sysname,
is_disabled bit,
definition nvarchar(max)
);
Most of the columns are self-explanatory, except is_disabled. That’s there in case we are going to drop all the triggers and re-create them; we want to make sure that if it was disabled before then it remains disabled. Most of the other properties in sys.triggers are facets of the trigger’s definition.
Next, let’s populate the table:
USE AdventureWorks; -- for example
GO INSERT #triggers
(
[database],
[schema],
[object],
name,
is_disabled,
definition
)
SELECT
DB_NAME(),
s.name,
o.name,
t.name,
t.is_disabled,
m.definition
FROM sys.triggers AS t
INNER JOIN sys.sql_modules AS m
ON t.object_id = m.object_id
INNER JOIN sys.objects AS o
ON t.parent_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE parent_class = 1; -- OBJECT_OR_COLUMN
That’s the hard part. Once we’ve saved off these definitions, we can generate all kinds of scripts from them, as long as we’ve guarded against them changing in the meantime. And we can do this from the context of any database on the same instance. In all of these examples, I show PRINT and a commented EXEC; keep in mind that if you have a lot of triggers and/or they’re large, you won’t always be able to preview the scripts this way. See this tip for workarounds.
Disable all SQL Server Triggers
If you want to disable all the triggers, you can generate the script like this, keeping in mind that you don’t have to disable any triggers that are already disabled:
DECLARE @db sysname = N'AdventureWorks'; DECLARE @sql nvarchar(max) = N'',
@exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql '; SELECT @sql += N'DISABLE TRIGGER '
+ QUOTENAME([schema]) + N'.' + QUOTENAME([name]) + N' ON '
+ QUOTENAME([schema]) + N'.' + QUOTENAME([object]) + N';'
FROM #triggers
WHERE is_disabled = 0
AND [database] = @db; PRINT @sql;
-- EXEC @exec @sql;
Enable all SQL Server Triggers
To generate the script to (re-)enable the triggers is remarkably similar:
DECLARE @db sysname = N'AdventureWorks'; DECLARE @sql nvarchar(max) = N'',
@exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql '; SELECT @sql += N'ENABLE TRIGGER '
+ QUOTENAME([schema]) + N'.' + QUOTENAME([name]) + N' ON '
+ QUOTENAME([schema]) + N'.' + QUOTENAME([object]) + N';'
FROM #triggers
WHERE is_disabled = 0
AND [database] = @db; PRINT @sql;
-- EXEC @exec @sql;
Drop all SQL Server Triggers
Sometimes you want to just drop all the triggers. Again, with our initial template set up, it is trivial to adjust for this:
DECLARE @db sysname = N'AdventureWorks'; DECLARE @sql nvarchar(max) = N'', @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql '; SELECT @sql += N'DROP TRIGGER ' + QUOTENAME([schema]) + N'.' + QUOTENAME([name]) + N';' FROM #triggers WHERE is_disabled = 0 AND [database] = @db; PRINT @sql; -- EXEC @exec @sql;
Create all SQL Server Triggers
In this case, you will likely want to combine a script that creates all the triggers, then sets the ones that were disabled before to disabled again:
DECLARE @db sysname = N'AdventureWorks'; DECLARE @sql nvarchar(max) = N'',
@exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql '; SELECT @sql += definition + CHAR(13) + CHAR(10) + N'GO' + CHAR(13) + CHAR(10)
FROM #triggers;
SELECT @sql += N'DISABLE TRIGGER '
+ QUOTENAME([schema]) + N'.' + QUOTENAME([name]) + N' ON '
+ QUOTENAME([schema]) + N'.' + QUOTENAME([object]) + N';'
FROM #triggers
WHERE is_disabled = 1 /***** important switch here *****/
AND [database] = @db; PRINT @sql;
-- EXEC @exec @sql;
Conclusion
With this simple script template, you can perform all kinds of actions against all (or some subset) of your triggers. Currently you have to handle a single database at a time, but with a little more complexity and some nested dynamic SQL, you could also automate the script generation to handle multiple databases simultaneously.
Next Steps
Read on for related tips and other resources:
- Convert Implicit and the related performance issues with SQL Server
- Concatenation of Different SQL Server Data Types
- SQL Server performance issues when using mismatched data types
- How to read SQL Server graphical query execution plans
- All SQL Server Trigger Tips
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips