RE: Cascading Triggers

Carol,
As long as the triggers aren't declared as autonomous transactions, they'll see all the previous changes that the session made.

Are the two columns in the where condition maybe two date columns loaded with different times?, or is one a varchar and the other a char so that the comparison isn't evaluating to true?
(BTW, I don't understand why the duveloper is comparing old.column1 with
new.column2 - doesn't the equivalent new.column1 = new.column2 read better - but that shouldn't make a difference. ) A null value for old.col-2 should also not affect the comparison.

Maybe the trigger-2 is firing, but for some reason the update isn't finding the row you want to update?

Chaim

"Mercadante, Thomas F" <[EMAIL PROTECTED]>@fatcity.com on 06/09/2003 03:45:17 PM

Please respond to [EMAIL PROTECTED]

Sent by: [EMAIL PROTECTED]

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc:

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-----
Sent: Monday, June 09, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L

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

[IMAGE]
(See attached file: ATT154058.gif)

<<attachment: ATT154058.gif>> Received on Mon Jun 09 2003 - 14:35:51 CDT