Disable Triggers SQL

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
LVL 2
kintonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tomvergoteCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jacobhooverCommented:
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
tomvergoteCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jacobhooverCommented:
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
tomvergoteCommented:
@kinton...
how about some feedback
0
jacobhooverCommented:
Any feedback would be nice..
0
kintonAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.