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?
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):
UPDATE <TABLENAME> WITH (ROWLOCK) SET <Column>='VALUE' WHERE ID=1
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.
Check out this thread:
http://stackoverflow.com/questions/495629/mssql-disable-triggers-for-one-insert
It offers a number of possible solutions but none are straight forward 'disable triggers for my individual session'.
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).
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.
hi friend,
to disable trigger :
DISABLE TRIGGER trigger_create
to enable trigger:
enable TRIGGER trigger_create
regards,
rathnakar
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.