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;
    


Learn more tricks

Add Comment

Comments

27th August 2021

propecia prescription online

Gursefs

14th September 2021

Zithromax

Sturoky