No Cascade on Before Trigger firing other triggers

[Jack]

We are have a question about the no cascade option on before triggers.
The description stays that no other triggers will be fired by the
changes of a before trigger. One of our developers is seeing results
that would imply that other after triggers are being fired by the
results of a before trigger.

Is this possible? Is there some little documented behavior going on
here?

thanks

Jack Baker

[Jack]

Thanks Serge

What you describe makes sense and is consistent with the behavior we are seeing.

jb

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c9**********@hanover.torolab.ibm.com>...

Jack,

Best illustrated with an example:

CREATE TABLE T(c1 INT, c2 INT);

INSERT INTO T VALUES (1, 1);

CREATE TRIGGER trg1 NO CASCADE BEFORE UPDATE OF c1 ON T
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET new.c2 = new.c1;

CREATE TRIGGER trg2 NO CASCADE BEFORE UPDATE OF c2 ON T
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET new.c1 = new.c2 + 1;

CREATE TRIGGER trg3 AFTER UPDATE OF c2 ON T
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
INSERT INTO T VALUES (-new.c1, -new.c2);

UPDATE T SET c1 = 5;

SELECT * FROM T ORDER BY c1;
=> (5, 5), (-5, -5)

So what happend? The SET statement in trg1 effectively added c2 to the
list of updated values, however this knowledge did NOT CASCADE to trigger2.
That one only bothers with the original SET clause of the update,
otherwise the SET statement in trg2 could send the system into a forever
cascade.
The AFTER trigger on the other hand sees whatever effectively happend to
the row.
Note that cascading for after triggers isn't an issue since you can't
SET the values after the fact. You can "only" cause recursion with
embedded UPDATE statements.
So if you saw after triggers firing that would be as designed.

Cheers
Serge

[Serge Rielau]

*phew*You got me sweating there for a momment ;-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab