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).
