Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    21

    Unanswered: Disabling triggers

    advice please anyone,
    i know you can disable triggers from within stored procedures like:
    Code:
    DISABLE TRIGGER Audit_P_Ops_Update ON P_Ops;
    but i want to know if there is any way of doing this but having it disabled just for the instance of the stored procedure.
    what i mean is if i run the stored procedure which disables it, if someone else ran an update query on the table where the trigger is held, to have the trigger enabled for them, as there is a chance that these things could happen in sync.

    thank in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you give us a bit more background information please? I can't think of a reason why you'd want to [regularly] disable a trigger temporarily?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    If your trigger code doesn't apply to every update then it doesn't belong in a trigger. I suggest you put it in a stored procedure instead. There is no selective disabling of triggers. They are either enabled or disabled for a table.

    Poorly thought out triggers seem to be a common problem unfortunately.

  4. #4
    Join Date
    Sep 2005
    Posts
    21
    The trigger was added for auditing purposes, but there is one stored procedure that does quite alot of updates to a table, and this is creating about 5 records in the audit table.
    i was just wanting a way to stop that happening for this specific stored procedure...

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What we once did, was to add a column to the table with the name "disable_trigger".
    If any part of the application wanted to insert or update a row without firing the trigger, that column was simply set to 1.

    In the trigger this was checked. If it was 1 it was reset to 0 and then the trigger finished, otherwise it would proceed normally.

    Not very nice, but it worked

  6. #6
    Join Date
    Sep 2005
    Posts
    21
    Thanks for that, but i have decided to leave the trigger alone and have modified the stored procedure that brought this matter to my attention.
    anyhoo, all is now well and working. thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •