Script triggers from any database in SQL Server

By:   |   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:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand 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



Comments For This Article




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

Wednesday, July 21, 2021 - 10:28:40 AM - Mike Petrak Back To Top (89033)
Thanks, Aaron. This helped me with something I'm testing.

I tried the DROP TRIGGER script but got failures near "dbo". I removed the ON schema.object part at the end and that worked:

+ N' ON '
+ QUOTENAME([schema]) + N'.' + QUOTENAME([object]) + N';'

I checked the DROP TRIGGER syntax at https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-trigger-transact-sql?view=sql-server-ver15 and it is just

-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name [ ,...n ] [ ; ]

Thursday, June 7, 2018 - 1:59:32 PM - Aaron Bertrand Back To Top (76153)

 

Scott, sure, but there are plenty of use cases I didn’t exhaustively go through, that the simpler variation would thwart:

- auditing “last action” on a trigger. If a trigger was already disabled you might not want to disable it again. In fact you may not want to bother disabling a disabled trigger just out of principle. Scripting individually allows you to leave disabled triggers out. Or triggers with a certain naming convention, or just instead of triggers, or just delete triggers.

- re-enabling all triggers blindly ignores the possibility that some triggers were disabled prior to any change. One of the main purposes of this post was to show how to leave those triggers in the same state you found them, including previously disabled.


Thursday, June 7, 2018 - 1:25:46 PM - ScottPletcher Back To Top (76152)

To disable and enable all triggers on a table, I prefer:

ALTER TABLE dbo.table_name DISABLE TRIGGER ALL;

...
ALTER TABLE dbo.table_name ENABLE TRIGGER ALL;















get free sql tips
agree to terms