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

[cjard]

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?

[DaPi]

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?

[DaPi]

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.)