Re: Triggers seem to be compiled only randomly

From: <sybrandb_at_yahoo.com>
Date: 3 Aug 2005 05:48:21 -0700
Message-ID: <1123073301.857294.207460@o13g2000cwo.googlegroups.com>

As to multiple triggers
1 Oracle doesn't guarantee the order of the triggers. This may be irrelevant in your case, but you'd better not create F(ear)U(ncertainty) and D(oubt) in your system. 2 There is a concern with respect to this design. You state the trigger concerns a cascading update. I would like to be sure whether the cascading update fully succeeded or not. In your case either trigger can fail. No harm done when the first trigger fails, but Oracle won't roll back the results of the first trigger if the second fails (as Oracle has *statement* level rollback by default). So in combination with the first issue, you can never be sure which part failed, when you have a failure.
So the entire thing would need to go into one trigger like this

CREATE TRIGGER SABISDAT.itzVerarbeitungsSchlU­C0 after update of id -- I deliberately changed this

ON SABISDAT.tzVerarbeitungsSchl
   ^^^^^^^^^

FOR EACH ROW
BEGIN
    UPDATE
      tbBuchungen
    SET
      cIDVerarb = :NEW.cID
    WHERE
      cIDVerarb = :OLD.cID;

    UPDATE
      tbBuchungsVorlagen
    SET
      cIDVerarb = :NEW.cID
    WHERE
      cIDVerarb = :OLD.cID;
exception
when others then
rollback; -- roll back the transaction
raise; -- reraise the error to end-user
END; The trigger needs to be an after update trigger, because the master update may fail.
The triggers needs to fire only when the id changed. In that case you don't need have to test anymore.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA

Received on Wed Aug 03 2005 - 07:48:21 CDT