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

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Thu, 03 May 2001 12:51:06 -0700
Message-ID: <F001.002F909B.20010503125537@fatcity.com>

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:

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

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------