Disable Triggers SQL


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.

Who is Participating?
tomvergoteConnect With a Mentor Commented:
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

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.

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

jacobhooverConnect With a Mentor Commented:
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.

how about some feedback
Any feedback would be nice..
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".
All Courses

From novice to tech pro — start learning today.