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