Carol,
I see
two possible issues here:
1). Is the COL-2 value changing either from/to a
null value? if so, then the WHEN clause is incorrect and the Table B
trigger will not fire at all. In other words, if COL-2's before or after
value is a NULL, then the trigger will not fire. You should make sure and
include the NVL function in the WHEN clause.
2). The way I see it, if COL-2 is being updated
with a non-null value, that is not equal to the old value, then the trigger will
not fire. The WHEN clause says to ONLY fire if the before and after values
are the same. It looks like it should work.
Good
Luck!
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, June 09, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Cascading Triggers
Hi listers...
I have a question related to triggers. One of the developers here has created two triggers as follows :
Trigger #1 is an AFTER UPDATE trigger on TABLE-A
- the trigger has PL/SQL code including an UPDATE dml statement on TABLE-B.COL-2
Trigger #2 is an AFTER UPDATE trigger on TABLE-B with a WHEN clause (old.COL-1 = new.COL-2)
- the trigger has PL/SQL including an UPDATE to TABLE-C
So, here's the interesting thing...
An update to TABLE-A fires Trigger #1 causing an update to TABLE-B.COL-2.
This should in turn cause Trigger #2 to fire. But that is not happening, or perhaps its fired, but
the WHEN clause condition on TABLE-B (Trigger #2) isn't being met for some reason.
The question is why ? Is there something going on with read consistancy ? Where Trigger #2 sees
the old (pre-updated) version of TABLE-B.COL-2 and is not aware of the new value set by Trigger #1 ?
I haven't seen much info on cascading triggers.
Any thoughts or suggestions to point me in the right direction would be appreciated.
Carol
<<attachment: ATT154058.gif>>
