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:
1. 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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
