Search
Monday, November 12, 2018 ..:: Home ::.. Register  Login
   Calendar  
     
  
   Search  
     
  
   Blogroll  
     
  
   Disclosure  
All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging.
     
  
   TechTidBits (Blog)  

How do you disable all triggers on a specific table?

Feb 21

Written by:
Friday, February 21, 2014 4:56 PM  RssIcon

TSqlFundamentals 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!

 

Resources

MSSQLTips.com: Disabling a Trigger for a Specific SQL Statement or SessionDisabling a Trigger for a Specific SQL Statement or Session

Tags:
Categories:
Location: Blogs Parent Separator TechTidBits

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 
     
  
Copyright 1999-2012 by PCHenry.com   Terms Of Use  Privacy Statement