Disable triggers on specific sp

Question

Post reply

Disable triggers on specific sp

jrspano

Old Hand

Points: 313
More actions
October 21, 2003 at 7:42 am

#160178

Is there a good way to disable triggers on a table for a specific SP? When I run sp_xyz which updates 1 table, I want the triggers on the table not to fire. I want them to fire on all other updates and inserts.
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=16669&FORUM_ID=8&CAT_ID=1

--Jonathan

mrgaurav_gupta SSC Rookie Points: 46 More actions · Answer 2

Hi.

Yes u can achieve this.

What I wld like to suggest u is that use this statement as the statement before u r firing the update query.

alter table tablename disable trigger triggername

then fire the update query

and then again enable the trigger

alter table tablename enable trigger triggername

Hope that Solves ur Problem.

Gaurav Gupta

Database Developer

Advent Matrix Inc

.........

steven powell Mr or Mrs. 500 Points: 557 More actions · Answer 3

quote:
tablename disable trigger triggername
then fire the update query
and then again enable the trigger
alter table tablename enable trigger triggername

won't that disable the trigger for ALL updates rather than just for that proc?

surely a less bad way would be to add a column to the table which would hold procName or procId and test for that in the trigger...

vijaygill SSC-Addicted Points: 464 More actions · Answer 4

Using the alter table ???? disable trigger ???? is totally crap. Shouldn't the trigger fire when some other procedure executes for which it SHOULD fire?

I have implemented the same effect by using login name of the user in whose context this trigger might fire. If it matches the name for which i do not want it to fire, the trigger just exits. My requirements were simple so I went for login name, but the idea of creating role is even better and flexible.

buddy__a Hall of Fame Points: 3222 More actions · Answer 5

Here's the final post in the thread that Jonathan referenced.

quote:
For us it is a dead issue now. The developers are going to do some upfront validation which will eliminate the need for the trigger in question.

I think this is the best approach, if at all possible.

jrspano Old Hand Points: 313 More actions · Answer 6

Thanks for the replys guys. I think I'm going to go with the context idea in the link on the first reply. I can't change the table and don't have control over the app that writes to the table to be able to get rid of the trigger.