I ran a few tests of this on both a 7.3.4 and 8.1.6 database and found there
is definitely a difference in the way Oracle handles FK constraints. In my
tests, I created a PARENT and CHILD table, with FK constraint, and with a
trigger on the CHILD. I used two differenct after update triggers: one slept
for 2 seconds (sleepy), one inserted old and new data into a RESULTS table.
I then ran two updates, one successful and one unsuccessful due to the FK.
Tracing was on.
I'll paste parts of the trace files at the end. The main points are that:
In 7.3.4 Oracle checks the FK by running SELECT null FROM parent
WHERE id=:1
If this succeeeds, run the trigger, otherwise get the FK name for
the error message
In 8.1.6 the trigger is run regardless, and rolled back if
necessary. I see the Insert for the RESULTS table even
when the FK is violated. I also see the extra 2 second wait in the
trace file for my sleepy trigger. The problem is
that the entries in the RESULTS table can be rolled back, but not
the 2 second sleep.
Sounds like a bug to me.
Henry
sqlplus system/manager <<EOF
DROP TABLE child_temp
/
DROP TABLE parent_temp
/
CREATE TABLE parent_temp (id number)
/
CREATE TABLE child_temp (id number)
/
ALTER TABLE parent_temp
ADD CONSTRAINT pk_id
PRIMARY KEY (id)
/
ALTER TABLE child_temp
ADD CONSTRAINT fk_id
FOREIGN KEY (id) REFERENCES parent_temp
/
INSERT INTO parent_temp VALUES (1)
/
INSERT INTO parent_temp VALUES (2)
/
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4'
/
INSERT INTO child_temp VALUES (1)
/
UPDATE child_temp SET id=2 WHERE id=1
/
UPDATE child_temp SET id=3 WHERE id=2
/
EOF
sqlplus system/manager <<EOF
CREATE OR REPLACE TRIGGER sleepy
AFTER UPDATE ON system.child_temp
FOR EACH ROW
BEGIN
dbms_lock.sleep(2);
END;
/
/* or use
DROP TABLE results_temp
/
CREATE TABLE results_temp (old number,
new number)
/
CREATE OR REPLACE TRIGGER results
AFTER UPDATE ON system.child_temp
FOR EACH ROW
BEGIN
INSERT INTO results_temp VALUES (:old.id, :new.id);
END;
/ */
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4'
/
UPDATE child_temp SET id=1 WHERE id=2
/
UPDATE child_temp SET id=3 WHERE id=1
/
EOF
Edited trace files
Oracle7 Server Release 7.3.4.0.1 - Production
=====================
PARSING IN CURSOR #1 len=38 dep=0 uid=5 oct=6 lid=5 tim=12613128
hv=710801591 ad='801c1e34'
UPDATE child_temp SET id=1 WHERE id=2
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=3 lid=0 tim=12613128
hv=2218816300 ad='802ecc4c'
select null from "SYSTEM"."PARENT_TEMP" where "ID" = :1
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=12613128
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=12613128
=====================
PARSING IN CURSOR #4 len=112 dep=2 uid=0 oct=3 lid=0 tim=12613130
hv=2862726689 ad='80301e5c'
select u.name,o.name from obj$ o,user$ u,trigger$ t where t.baseobject=:1
and t.obj#=o.obj# and o.owner#=u.user#
=====================
PARSING IN CURSOR #3 len=44 dep=1 uid=5 oct=2 lid=5 tim=12613130
hv=1893271645 ad='801b8e90'
INSERT INTO RESULTS_TEMP VALUES ( :b1,:b2 )
=====================
PARSING IN CURSOR #2 len=124 dep=1 uid=0 oct=3 lid=0 tim=12613131
hv=1953436077 ad='802f0a9c'
select i.file#, i.block#, i.cols, s.groups from ind$ i, seg$ s where obj# =
:1 and i.file# = s.file# and i.block# = s.block#
=====================
PARSING IN CURSOR #1 len=38 dep=0 uid=5 oct=6 lid=5 tim=12613131
hv=710800563 ad='801b321c'
UPDATE child_temp SET id=3 WHERE id=1
=====================
PARSING IN CURSOR #2 len=56 dep=1 uid=0 oct=3 lid=0 tim=12613131
hv=2218816300 ad='802ecc4c'
select null from "SYSTEM"."PARENT_TEMP" where "ID" = :1
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=12613131
FETCH #2:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=12613131
=====================
PARSING IN CURSOR #3 len=80 dep=1 uid=0 oct=3 lid=0 tim=12613131
hv=3971794324 ad='802a67e8'
select o.name, c.name from con$ c, user$ o where c.con# = :1 and owner# =
user#
XCTEND rlbk=0, rd_only=0
XCTEND rlbk=0, rd_only=1
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PARSING IN CURSOR #1 len=38 dep=0 uid=5 oct=6 lid=5 tim=168651643
hv=1710742153 ad='82d88fa4'
UPDATE child_temp SET id=1 WHERE id=2
END OF STMT
=====================
PARSING IN CURSOR #3 len=48 dep=2 uid=0 oct=3 lid=0 tim=168651645
hv=1005331575 ad='82dda048'
select user# from sys.user$ where name = 'OUTLN'
END OF STMT
PARSE #3:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=168651645
BINDS #3:
=====================
PARSING IN CURSOR #3 len=175 dep=2 uid=0 oct=3 lid=0 tim=168651645
hv=1491008679 ad='82f1293c'
select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from obj$
o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and
o.owner#=u.user# order by o.obj#
=====================
PARSING IN CURSOR #2 len=44 dep=1 uid=5 oct=2 lid=5 tim=168651646
hv=1493239216 ad='82d9436c'
INSERT INTO RESULTS_TEMP VALUES ( :b1,:b2 )
END OF STMT
EXEC #2:c=0,e=0,p=0,cr=1,cu=6,mis=0,r=1,dep=1,og=4,tim=168651646
EXEC #1:c=1,e=3,p=0,cr=6,cu=14,mis=0,r=1,dep=0,og=4,tim=168651646
=====================
PARSING IN CURSOR #1 len=38 dep=0 uid=5 oct=6 lid=5 tim=168651646
hv=1322531977 ad='82d824e4'
UPDATE child_temp SET id=3 WHERE id=1
=====================
PARSING IN CURSOR #2 len=44 dep=1 uid=5 oct=2 lid=5 tim=168651646
hv=1493239216 ad='82d9436c'
INSERT INTO RESULTS_TEMP VALUES ( :b1,:b2 )
END OF STMT
EXEC #2:c=0,e=0,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=4,tim=168651647
=====================
PARSING IN CURSOR #3 len=80 dep=1 uid=0 oct=3 lid=0 tim=168651647
hv=1351631542 ad='82edfd2c'
select o.name, c.name from con$ c, user$ o where c.con# = :1 and owner# =
user#
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=168651647
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=168651647
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=1,og=4,tim=168651647
EXEC #1:c=1,e=1,p=0,cr=7,cu=7,mis=0,r=0,dep=0,og=4,tim=168651647
ERROR #1:err=2291 tim=168651647
XCTEND rlbk=0, rd_only=0
-----Original Message-----
Sent: Thursday, May 03, 2001 4:56 PM
To: Multiple recipients of list ORACLE-L
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).
--
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).
