Here's an example.
create or replace trigger donuttin
after update on ian.test2
for each row
Declare
fhandle UTL_FILE.FILE_TYPE;
file_text varchar2(50);
Begin
fhandle :=
UTL_FILE.FOPEN('/tmp', 'utlfile.txt', 'A');
UTL_FILE.PUT_LINE(fhandle, 'old is '||:old.isint ||' new is ' ||:new.isint);
utl_file.fclose(fhandle);
end;
The triggering statement
update test2 set isint = 4 where isint = 3
*
ERROR at line 1:
ORA-02291: integrity constraint (IAN.TEST2_FK) violated - parent key not found
The isint name is not significant. No testing for integers is being done.
What's written to the file
SQL> host cat /tmp/utlfile.txt
old is 3 new is 4
-----------------------------------------------------------------------------------------------
The problem doesn't only occur on a foreing key constraint violation, but also
violations ofcheck constraints and unique constraints, and I imagine primary
constraints as well for insert operations. Coding for all this would be very very
ugly. The code needs to be dynamic enough so it reflects the present contraints, and
mutating table problemn workarounds would need to be implemented.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-----Original Message-----
Sent: Thursday, May 03, 2001 10:42 AM
To: Multiple recipients of list ORACLE-L
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).
--
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).
