I have a INSERT, UPDATE trigger on a table, it works great and helps me from firing a stored procedure from all the (think it's over 50places) where it is needed.

But I have one batch update to this table, that doesn’t require the trigger procedure; the trigger is very heavy so I would like to make an exception only in this update. Is it possible to write something into the UPDATE statement or do I have to disable and enable the trigger every time this update runs?

8 Years
Discussion Span
Last Post by rathnakar

Just to be clear, I’m thinking about something like this (except that I want to skip trigger instead of setting the update to only lock current row):

I have tried to put anything that comes into my mind after WITH, but still no luck.
Google haven't been at much help eiter.


It's kind of same solution I’m implementing now.
I have added a column called "TUpdate" it's a bit column that i set to true in all the different update statements. This solution will not use much load on the server, because the first thing i do in the trigger is to check if the value in INSERTED is True, if it is then it continues to do the work in the trigger, and it updates this column to be false in the end. It will take some space in the database, because i have around 11,5 million rows, but it doesn’t mater that much.

But this is not the optimal solution, I have been looking for this many times. Many people or talking about using individual rights, where they check what user that fires the trigger, and i can see why it works for many people. But it's a loot of work to implement, and like i mentioned, I would like to use this many places.


I'm verry close now.

For INSERT together with OPENROWSET you can use WITH (IGNORE TRIGGERS).

Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET.

Specifies that any triggers defined on the table are ignored by the bulk-import operation. By default, INSERT applies triggers.

Use IGNORE_TRIGGERS only if your application does not depend on any triggers and maximizing performance is important.

Votes + Comments
good find. I did not know that

hi friend,
to disable trigger :
DISABLE TRIGGER trigger_create
to enable trigger:
enable TRIGGER trigger_create


This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.