SQL Server Performance Forum – Threads Archive

Triggers in SQL 2005

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?

---

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.

---

]]>