SQL Server Performance

Triggers in SQL 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by sql_jr, Oct 20, 2006.

  1. sql_jr New Member

    Has SQL/Server 2005 added any syntax for executing an insert / update or delete and suppressing or bypassing triggers?

    There are many instances when we are mass updating thousands of rows, and we KNOW we don't need the triggers to run. However, there is no way to bypass the triggers JUST FOR THIS ONE COMMAND. Or is there?

    Example:

    insert WITH NO INSERT TRIGGER WITH NO UPDATE TRIGGER returns (month, ror) values ('12/2006',.00123)

    Or some such syntax, do you know of any solutions?
  2. Adriaan New Member

    In SQL 2000, you can do this:

    ALTER TABLE <tbl_name> DISABLE TRIGGER <trigger_name>
    GO

    <your action query>

    ALTER TABLE <tbl_name> ENABLE TRIGGER <trigger_name>
    GO

    Instead of the trigger name, you can also use the ALL keyword to deactivate all triggers on the table.

    You do need elevated permissions to disable and enable triggers.

Share This Page