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?
