Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script triggers from any database in SQL Server


By:   |   Last Updated: 2018-05-30   |   Comments (2)   |   Related Tips: More > 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' ON ' 
  + QUOTENAME([schema]) + N'.' + QUOTENAME([object]) + 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:



Last Updated: 2018-05-30


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, June 07, 2018 - 1:59:32 PM - Aaron Bertrand Back To Top

 

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 07, 2018 - 1:25:46 PM - ScottPletcher Back To Top

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;


Learn more about SQL Server tools