On our WANG system, we used a character-string as an argument to determine if a trigger should run through it's code. In Oracle, this argument has turned into a package specification variable.
Our WANG code has this variable set back to NULL at the end of the on row trigger, and so the code converted by our vendor does as well. This does not work well when performing a multi-record update/insert/delete statement:
update salary set income = income * (income * .10);
So, one option we're considering is adding an AFTER STATEMENT trigger, and resetting this package spec variable back to NULL there. Because of our inexperience, though, we're unsure if there's any coding method which can update single records in a serial fashion without invoking the AFTER STATEMENT trigger until after all rows are updated. Perhaps something like an updatable cursor or something?
The hypothetical situation I was trying to look out for would be if something like the following would cause the after statement trigger to fire at an unexpected time, such as after the LOOP was executed (I was thinking that perhaps an updatable cursor might cause something like this):
CURSOR c1 IS SELECT age, myname, mod_id FROM DEBUG_TRIGGER FOR UPDATE NOWAIT;
FETCH c1 INTO v_age, v_myname, v_mod_id;
UPDATE DEBUG_TRIGGER SET age = 21 WHERE CURRENT OF c1;
EXIT WHEN c1%NOTFOUND;
If I had to guess would be that each UPDATE statement would be a separate statement, and so the after stmt trigger would fire for each row in the cursor. I just can't get this final piece of test code working in order to test it.
It fails on the 'UPDATE' line with the error:
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 11
If someone has a suggestion on what I've done wrong, that'd be cool too.