We have a table with a BEFORE ... FOR EACH ROW trigger which populates a PL/SQL table with the rows being inserted into the table, so that an AFTER STATEMENT trigger can perform some additional processing. The whole setup helps us avoid a mutating table error.
We found out recently that when an insert violates the referential integrity, the RI check occurs after the before trigger. So, the PL/SQL table retains the record with the duplicate primary key. When the insert stmt is corrected to have a unique primary key and re-submitted, it's added to the PL/SQL table in the before trigger, and now we're facing a situation where the AFTER STATEMENT trigger processes both the erroneous record and the 'real' record.
Has anyone encountered this before? Or can you think of a way that we can purge the PL/SQL table when a CONSTRAINT is violated? We could get it to work when only one record is inserted at a time (using package spec variables), but when we considered that multiple rows might be inserted/updated per single SQL statement, we got stumped.
I think we're still going to try and use SPEC variables, and clear the PL/SQL tables in a BEFORE statement-level trigger when that trigger is NOT reached via DML initiated by the AFTER statement-level trigger.
I guess because we're performing data checks, and in some cases altering data prior to the DML actually being performed. Most of our trigger code has come from a vendor, and our lack of experience in Oracle allowed them to place everything in a BEFORE ... FOR EACH ROW trigger, rather than evaluate if an AFTER ... FOR EACH ROW could be used instead.
Is there a short list of reasons why an AFTER ... FOR EACH ROW trigger could be used instead of the BEFORE... FOR EACH ROW , aside from the problems encountered in my post? I have seen posts about AFTER ... FOR EACH ROW triggers being faster.
There could be a BEFORE ROW trigger to alter values and an AFTER ROW trigger to note rows actually inserted (subject to rollback of the entire statement of course).
I think AFTER ROW triggers are only "faster" in the sense that they do not fire if the insert fails due to a constraint violation, so failed inserts fail quicker. Successful inserts would take the same time either way. Another place to consider an AFTER ROW trigger is when you want to take other actions (or validations) that depend on the new values - because in a BEFORE ROW trigger those values are still potentially changeable (by other triggers).