Update a table without firing the update trigger

Posted on 2007-10-17
Last Modified: 2010-04-22
I have a table with an update trigger. I have a stored procedure that updates this table. If this spcific stored procedure does the update I DONT want the update trigger to fire. I can't drop and recreate the trigger because other program and procedures will be updating the table, and for those I want the trigger to fire. Any suggestions?
Question by:HBHoffman
    LVL 17

    Expert Comment

    If the trigger isn't processing a universal business rule it shouldn't be on the table.

    If your SP is encapsulating the same business rule as the trigger I'd rewrite the SP to not include it.
    LVL 8

    Author Comment

    So triggers are ONLY for processing "universal business"? What about logging before and after column values for auditing purposes.

    Im soliciting suggestions on how to resolve my issue, If you have one please post it, If not please dont waste my time.

    LVL 17

    Expert Comment

    Yes, triggers are supposed to be ONLY for universal business rules.  Why else would you place a trigger on a table?  They can certainly be used for auditing purposes.  That's a great reason to use them.

    Your issue can't be resolved in the way you want.  Triggers always fire unless you disable them.

    Sorry to waste your time.
    LVL 8

    Author Comment

    Where is it written that triggers are ONLY supposed to be used for applying universal business rules?

    I don't mean to be rude to you, but all too often people seem compelled to scold others for not doing things the way THEY think it should be done. I think the purpose of this community is to help people solve their problems not chastise them for taking a different approach.

    This solutions seems to work:
    In the sp that does the update (prior to the update);
    Write the SQL process Id to a table called SuspendTrigger, delete the row after all the updates.  

    In the Update Trigger;
    Check to see if its process Id is the same as what's in the SuspendTrigger table, if it is exit the trigger

    Please close this question
    LVL 17

    Accepted Solution

    As with any database design, nothing is written in hard and fast rules.

    There is however the concept of good design as born out of years of experience in the community.

    I didn't scold you nor did I chastise you.  I pointed out a potential design flaw.  I'm not the first to say that triggers should be used to process universal business rules.  Any DBA worth their salt will tell you this.

    The technique you found may indeed work...assuming nothing else the same SPID as your process before the DELETE from SuspendTrigger gets processed.  You may want to run the entire batch within an explicitly defined transaction.
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    you will need, inside the trigger, need to determinate, if the update comes from the procedure or not.
    one way would be to have a column in the table that identifies from where the update comes from...

    alter table yourtable add UpdateSource char(1) null

    create trigger trg_update
    on yourtable
    for update
      if exists ( select null from inserted i where i.UpdateSource = 'P' )
        -- just reset the field to null, but don't do anything else
        update yourtable
         set UpdateSource = null
        where updatesource = 'P'        
        -- do the normal coding ...

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    734 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

    22 Experts available now in Live!

    Get 1:1 Help Now