RE: Cascading Triggers

Thanks for the responses so far...

The transactions weren't declaraed as autonomous, so I, too, expect what you suggest. The two columns in question are of the same datatype -- numeric.

This is a strange one.

[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
06/09/2003 04:14 PM
Please respond to ORACLE-L

       
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        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)

The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.

Received on Mon Jun 09 2003 - 15:50:03 CDT