Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Disable Triggers SQL

Posted on 2004-10-22
9
Medium Priority
?
303 Views
Last Modified: 2010-04-17
Hi,

I am updating SQL 2000 tables through a VB application (connection via ado).  Is there a way I can disable the triggers on the table I am updating only within the application.  i.e.  If another user were to be updating the trigger would still exist for them.

thanks
0
Comment
Question by:kinton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
9 Comments
 
LVL 8

Accepted Solution

by:
tomvergote earned 1000 total points
ID: 12382409
I'm not aware of any way you can disable triggers for your connection
(aka when your script executes no triggers and in the meantime another users updates the table yes triggers)

What you can do is:
alter table <table_name> disable trigger {<trigger_name> | all}
-- your script goes here
alter table <table_name> enable trigger {<trigger_name> | all}

but that disables the trigger for the time your script is executing and for every user.

Another way, but it's messy would be to
- modify the table and add a bit field (skiptrigger or something)
- modify the trigger to skip fields where skiptrigger is set to true
- modify your script to set the skiptrigger flag before you update

0
 
LVL 7

Expert Comment

by:jacobhoover
ID: 12385856
Hmm, a possible UGLY about doing it would to be to modify the triggers to check the current username. (I know this can be done in Oracle)  Just make a seperate user called "SystemsUpdater" or something along those lines.  Then in your trigger just exit out if it is this user.  And as always, once your done doing the update, just disable the "SystemUpdater" user.

Jake
0
 
LVL 8

Expert Comment

by:tomvergote
ID: 12386031
jacobhoover's suggestion looks pretty nice too

i'm not on a system running sql server right now but according to http://groups.google.com/groups?hl=en&lr=&threadm=%23o%24rC2IwBHA.2412%40tkmsftngp04&rnum=13&prev=/groups%3Fq%3Dtsql%2Buser%2Bname%26hl%3Den%26lr%3D%26start%3D10%26sa%3DN you can use something like
DECLARE @UserName varchar(100)
SET @UserName = Current_User
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Assisted Solution

by:jacobhoover
jacobhoover earned 1000 total points
ID: 12386080
It all depends on the total number of triggers he has and if he can modify them.  My solution involves some initial work, but if you ever need to do updates again, you will all ready have the fondation in place.  Then, one could even create a simple table and a function to allow you fine control over triggers... Something like: (I'm an Oracle guy so this is psuedo logic)

Table: UserDisabledTriggers ( UserName, TableName)
Function: CheckTriggerOverride(Username,TableName) as Boolean
{
  Boolean blnState;
  SELECT COUNT(*) BLNEXISTS FROM UserDisabledTriggers INTO blnState WHERE USERNAME := Username AND TABLENAME := TableName;
}

Then the logic is if the User/Table combo exists in the table than DON'T execute the trigger.... Probably a little more complex than what you need, but just a thought.

Jake
0
 
LVL 8

Expert Comment

by:tomvergote
ID: 12533208
@kinton...
how about some feedback
0
 
LVL 7

Expert Comment

by:jacobhoover
ID: 12550129
Any feedback would be nice..
0
 
LVL 2

Author Comment

by:kinton
ID: 12724408
Appologies for the lack of feedback, i've been attending to another task and haven't managed to get back to this problem to look.  I'm hoping to be back on to it next week.

Thanks for your suggestions, and i'll try them all out ASAP and post my findings.  Appologies again for "the quietness".
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Six Sigma Control Plans

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question