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

Question

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.

Answer 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

Answer 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

Answer 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.

Answer 4

To extend Matt's answer, here is an example given on MSDN.

USE AdventureWorks;
GO
DISABLE TRIGGER Person.uAddress ON Person.Address;
GO
ENABLE Trigger Person.uAddress ON Person.Address;
GO

Answer 5

Another approach is to effectively disable the trigger without actually disabling it, using an additional state variable that is incorporated into the trigger.

create trigger [SomeSchema].[SomeTableIsEditableTrigger] ON [SomeSchema].[SomeTable]
for insert, update, delete 
as
declare
    @isTableTriggerEnabled bit;

exec usp_IsTableTriggerEnabled -- Have to use USP instead of UFN for access to #temp
    @pTriggerProcedureIdOpt  = @@procid,    
    @poIsTableTriggerEnabled = @isTableTriggerEnabled out;

if (@isTableTriggerEnabled = 0)
    return;

-- Rest of existing trigger
go

For the state variable one could read some type of lock control record in a table (best if limited to the context of the current session), use CONTEXT_INFO(), or use the presence of a particular temp table name (which is already session scope limited):

create proc [usp_IsTableTriggerEnabled]
    @pTriggerProcedureIdOpt  bigint          = null, -- Either provide this
    @pTableNameOpt           varchar(300)    = null, -- or this
    @poIsTableTriggerEnabled bit             = null out
begin

    set @poIsTableTriggerEnabled = 1; -- default return value (ensure not null)

    -- Allow a particular session to disable all triggers (since local 
    -- temp tables are session scope limited).
    --
    if (object_id('tempdb..#Common_DisableTableTriggers') is not null)
    begin
        set @poIsTableTriggerEnabled = 0;
        return;
    end

    -- Resolve table name if given trigger procedure id instead of table name.
    -- Google: "How to get the table name in the trigger definition"
    --
    set @pTableNameOpt = coalesce(
         @pTableNameOpt, 
         (select object_schema_name(parent_id) + '.' + object_name(parent_id) as tablename 
           from sys.triggers 
           where object_id = @pTriggerProcedureIdOpt)
    );

    -- Else decide based on logic involving @pTableNameOpt and possibly current session
end

Then to disable all triggers:

select 1 as A into #Common_DisableTableTriggers;
-- do work 
drop table #Common_DisableTableTriggers; -- or close connection

A potentially major downside is that the trigger is permanently slowed down depending on the complexity of accessing of the state variable.

Edit: Adding a reference to this amazingly similar 2008 post by Samuel Vanga.

Answer 6

ALTER TABLE table_name DISABLE TRIGGER TRIGGER_NAME
-- Here your SQL query
ALTER TABLE table_name ENABLE TRIGGER TRIGGER_NAME

Answer 7

Not the best answer for batch programming, but for others finding this question in search of a quick and easy way to temporarily disable a trigger, this can be accomplished in SQL Server Management Studio.

1. expand the triggers folder on the table
2. right-click the trigger
3. disable

Follow the same process to re-enable.