SQL Server 2005: T-SQL to temporarily disable a trigger

Is it possible to disable a trigger for a batch of commands and then enable it when the batch is done?

I'm sure I could drop the trigger and re-add it but I was wondering if there was another way.

Solution 1:

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

http://msdn.microsoft.com/en-us/library/ms189748(SQL.90).aspx

followed by the inverse:

ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

http://msdn.microsoft.com/en-us/library/ms182706(SQL.90).aspx

Solution 2:

Sometimes to populate an empty database from external data source or debug a problem in the database I need to disable ALL triggers and constraints. To do so I use the following code:

To disable all constraints and triggers:

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"

To enable all constraints and triggers:

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER  all"

I found that solution some time ago on SQLServerCentral, but needed to modify the enable constraints part as the original one did not work fully

Solution 3:

However, it is almost always a bad idea to do this. You will mess with the integrity of the database. Do not do it without considering the ramifications and checking with the dbas if you have them.

If you do follow Matt's code be sure to remember to turn the trigger back on. ANd remember the trigger is disabled for everyone inserting, updating or deleting from the table while it is turned off, not just for your process, so if it must be done, then do it during the hours when the database is least active (and preferably in single user mode).

If you need to do this to import a large amount of data, then consider that bulk insert does not fire the triggers. But then your process after the bulk insert will have to fix up any data integrity problems you introduce by nor firing the triggers.