Solved

Disable Triggers SQL

Posted on 2004-10-22
291 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
Question by:kinton
    7 Comments
     
    LVL 8

    Accepted Solution

    by:
    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
    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
    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
     
    LVL 7

    Assisted Solution

    by:jacobhoover
    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
    @kinton...
    how about some feedback
    0
     
    LVL 7

    Expert Comment

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

    Author Comment

    by:kinton
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    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 …

    877 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now