How to UPDATE table_x inside a trigger that fires upon update of table_x ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to UPDATE table_x inside a trigger that fires upon update of table_x ?

  1. #1
    Join Date
    Mar 2006
    Posts
    74

    How to UPDATE table_x inside a trigger that fires upon update of table_x ?

    I have a table that, when any isert or update occurs, i want to update another record in the same table

    This is actually to work around a bug in a program whereby the wrong ID number is being passed in. I can map the wrong ID to the correct ID number quite easily. I then want to update the correct record using what I have calculated should be the correct ID. i dont know though, how to update the table from inside an update trigger because basically, the app says:

    UPDATE table_x SET active = N where ID = 123

    it should have said:

    UPDATE table_x SET active = N where ID = 456



    but in a trigger i can do this:

    SELECT correct_id INTO tmpCorrectID FROM map_table WHERE wrong_id = 123

    the 123 is found in :OLD.id for this invoke of the trigger


    the trigger would then invoke:
    UPDATE table_x SET active = :NEW.active WHERE id = tmpCorrectID


    but this will cause the trigger to fire again? how can the trigger, when invoked the second time, detect that it was invoked by the database (correctly) rather than the client app(incorrect id) ?


    To make things more complicated, the client contains another UPDATE query that is correct. The table contains 5 columns. The correct update that the client runs, updates columns 1,2,3 and 4.
    the incorrect update query that the client runs, only updates column 5. I dont think though, that I can tell (in the trigger) which query the client is running ? (it's not guaranteed that a particular column in :OLD and :NEW will be different, so i cant say
    IF old.column5 <> new.column5 then we_are_running_the_damaged_update)


    So, my problems:

    2 update queries
    the update query that may affect cols 1 2 3 4 is quoted corerectly by the client
    the update query that affects col 5 is quoted incorrectly
    i can map the incorrect query to correct values
    i dont know how to fire an update inside an update trigger without having the database die in a recursive snow of updates
    (related) i dont know how to communicate between two invokations of an update trigger


    anyone have any ideas?

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    This is actually to work around a bug in a program whereby the wrong ID number is being passed in.
    Short answer - fix the bug.

    What happens if you introduce a trigger, and then someone fixes the bug or introduces code that uses the correct ID in an update?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    To come back to your original question - I'm sure that you would not get a "loop" of updates, your trigger will fail with a "table is mutating" error.

    IF you can sort out the good from the bad updates (which is not sure since some front ends generate SET A=A updates) you could (perhaps):
    - rename table_x to table_x_base
    - create view table_x as select * from table_x_base
    - recompile 1'563 invalid objects
    - create an INSTEAD OF trigger on the view

    (Waiting to be told that's crazy.)
    Last edited by DaPi; 05-12-2006 at 10:46 AM.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

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



Click Here to Expand Forum to Full Width