Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: "After Update on <TABLE_NAME> For Each Row..." Triggers and

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

From: Henry Poras <Henry.Poras_at_ctp.com>
Date: Fri, 11 May 2001 12:30:39 -0700
Message-ID: <F001.003010B4.20010511085617@fatcity.com>

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
ian_at_slac.stanford.edu

Ian MacGregor
Stanford Linear Accelerator Center
uan_at_slac.stanford.edu

-----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
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
--------------------------------------------------------------------
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: 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
--------------------------------------------------------------------
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).
Received on Fri May 11 2001 - 14:30:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US