MS SQL Tips & Tricks

How to disable trigger on table

There are two possible solutions to disable trigger execution on particular table:

  1. DISABLE TRIGGER (available on SQL 2005 SP2 and above) 
    DISABLE TRIGGER [tiger_name | ALL]  on [table]

    e.g. 

    DISABLE TRIGGER TI_EMPLOYEES_IU on EMPLOYEES

    Above disables TI_EMPLOYEES_IU trigger on EMPLOYEES table.

    DISABLE TRIGGER ALL on EMPLOYEES

    Above disables all triggers on EMPLOYEES table.

    Once the triggers are disable You will probably need a way to enable it. Use construction:

    ENABLE  TRIGGER [tiger_name | ALL]  on [table]

    Enabling works the same way as disabling so the same examples and notes can be used.

  2. ALTER TABLE (available on MS SQL 2000 / 2005) 
    ALTER TABLE [table] DISABLE TRIGGER [trigger_name | ALL]
ALTER TABLE [table] ENABLE TRIGGER [trigger_name | ALL]

    E.g.

    ALTER TABLE EMPLOYEES DISABLE TRIGGER TI_EMPLOYEES_IU ;
ALTER TABLE EMPLOYEES DISABLE TRIGGER ALL;
ALTER TABLE EMPLOYEES ENABLE TRIGGER TI_EMPLOYEES_IU ;
ALTER TABLE EMPLOYEES ENABLE TRIGGER ALL;

See also


Learn more tricks

Add Comment

Comments