Andreas Mosmann wrote:
> 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.itzVerarbeitungsSchlUC0
>> 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?
>
By the time the triggers have fired, consistency can be checked.
Look up deferred foreign key constraints (initially deferred).
The database can handle on cascade delete foreign keys (but no cascade
updates), so there's no worry, either.
--
Regards,
Frank van Bortel
Received on Wed Aug 03 2005 - 14:51:52 CDT
