Re: Triggers seem to be compiled only randomly

sybrandb_at_yahoo.com schrieb am 03.08.2005 in <1123073301.857294.207460_at_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.
ok, you are right, in this case it is not important, but we surely should consider this

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

This is important in that case. The (existing) trigger presupposes that there will be no errors. This has to be changed.

> 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
I guess when the trigger producing script was written the author did not know this possibility.

> 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 script has to be changed.

> The trigger needs to be an after update trigger, because the master
> update may fail.

but tell me, if there is a foreign key- constraint, either before -> :new.(tzVerarbeitungsSchl)cid dosn't exist after -> if :old.(tzVerarbeitungsSchl)cid is to be killed there are depending records in tbBuchungen, tbBuchungsVorlagen ... Is this problem fundamentally solvable?

> The triggers needs to fire only when the id changed. In that case you
> don't need have to test anymore.

right

> Hth

Thanks a lot

Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de