Script triggers from any database in SQL Server

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:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Wednesday, July 21, 2021 - 1:05:09 PM - Aaron Bertrand	Back To Top (89036)
Mike, fixed, thanks for the correction!