How do you disable all triggers on a specific table?
Friday, February 21, 2014 4:56 PM
You have to do adhoc edits on a table but DON’T want to fire off specific triggers, how do you turn all of one tables triggers off?
So, you need to update\insert data into a table, but you KNOW it’s got something like two, three, 10, 11, or 16 triggers. DOH! You know there’s triggers, but you really don’t want to disable them one by one, and then have to re-enable them one by one afterwards. And heavens forbid you have to run this TWICE!!!!!!!!!!!!! DOH! WHAT would YOU do?
I tell what I do in my scripts, in one line, you can effectively disable ALL the triggers on table! BOOM! Done! Now you can update\insert\delete all you want. JUST make sure you re-enable them after you’re done! DOH! Don’t want to forget THAT part!!!!!!!!!!!!!!!!!!!!
alter table BigHonkingTable disable trigger all
--do your table updates\inserts\etc
alter table BigHonkingTable enable trigger all
With THIS much power does come with SOME responsibility! There is ONE hiccup to all this stuff!!!!!! You HAVE to be aware that you’re disabling ALL triggers on that table. “Ya, ok, Peter, I know that!” But, what happens if you’re running your script on a LIVE PRODUCTION DB with currently live customers/clients/people/web services accessing your db? DOH! Ya, they’re updates are also NOT issuing those trigger calls while you’ve disabled the triggers on that table. DOH! So this t-sql is DB wide, not session wide as you might have hoped.
IF you know you’re going to need this, you’ll need to create your triggers with using special Context_Info() syntax in your code. You’ll need to check it in your triggers and set it in your adhoc t-sql.
Now that you know how to quickly add/update data in your db, it’s time to grab a coffee and get coding!
MSSQLTips.com: Disabling a Trigger for a Specific SQL Statement or SessionDisabling a Trigger for a Specific SQL Statement or Session