Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: PL/SQL table and purging it

    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.

    Thanks,
    Chuck

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    in the before insert trigger ... could you

    select count(*) from other_table
    where key = key;

    if count(*) = 0
    then insert into other_table
    else update other_table
    where key = key;
    end if;

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    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.

    -Chuck

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Why not use an AFTER ... FOR EACH ROW trigger? That will only fire if the constraint check is passed and so the erroneous record won't be recorded.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    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.

    -Chuck

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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).

Posting Permissions

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