RE: "After Update on <TABLE_NAME> For Each Row..." Triggers and

From: Henry Poras <Henry.Poras_at_ctp.com>
Date: Thu, 03 May 2001 09:50:42 -0700
Message-ID: <F001.002F8A36.20010503094200@fatcity.com>

Sounds strange. I'd be curious at what values the a similar trigger would show if you captured :old and :new.

Henry

-----Original Message-----
Sent: Thursday, May 03, 2001 12:35 PM
To: Multiple recipients of list ORACLE-L R.I.

A developer has an "after update on <table_name> for each row ... " trigger which sends mail via the UTL_SMTP package. If the triggering statement is something like, "update saltab set dba_salary = dba_salary * 1000", and the resultant value is too large. The mail is not sent. However, if the statement is something like, "update saltab set foreign_key_column = 'QWERTY' where
foreign_key column = 'ASDFGH'", and an R.I. constraint violation occurs because there is no parent key, "ASDFGH", the mail is sent anyway.

I had thought that R.I. checking was done in the following manner for such a trigger:

execute triggering event; 2. check for R.I. violations resulting from step 1; 3. execute trigger logic; 4. check for R.I. violations caused by the trigger; 5. Repeat for each row.

(In this case the trigger just sends mail so step 4 can be removed).

If this sequence of events is correct why is the mail sent? Is the R.I. violation not placed on the error stack immediately? That seems unlikely. Is the error stack not read until after the mail is sent? It was read for a non-R.I. violation. Is Oracle programmed to defer reporting R.I. violations on the error stack until after he trigger logic is processed?

I could be wrong in my understanding of the trigger logic; step 2 is performed after step 3?

How does one stop the mail from being sent when an R.I. violation occurs. One way would be to do the RI checking in trigger via a cursor which queries the parent table, and then raises an exception if no parent key is found. Is there another way?

Ian MacGregor
Stanford Linear Accelerator Center
uan_at_slac.stanford.edu
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Henry Poras
INET: Henry.Poras_at_ctp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------