INSTEAD OF INSERT trigger and SELECT

From: Volker Apelt <gq437x_at_yahoo.de>
Date: Fri, 14 Mar 2003 15:25:55 +0100
Message-ID: <lgwuj2owi4.fsf@mu.biosolveit.local>

#Ora 920, Linux

One of our INSTEAD OF triggers suddenly raises exceptions when it should not.

That kind of trigger has worked for a long time without problems in many variations on different tables.

I see two possible explanations.

the database has a problem (tables or indexes corrupt ..) or
my trigger is wrong.

But, when I try to recreate the problem on a separate schema everything still works as expected.

Could one of you please check, if the concept of that trigger is flawed? Thank you.

Concept:
We insert into one table from many clients via mass inserts. Each row is unique and part of one unique constraint. The inserted rows can be duplicates of some already existing rows in the database. So, for dups we will recieve an ORA-0001 and the entire mass insert is rolled back. But, we need one and only one of them in the database (uniquenes).

Therefore, we use an INSTEAD OF INSERT trigger on a view based on that table and catch/ignore the duplicates there. So, we can use mass inserts which ignore existing duplicates.

Here is the concept of the trigger (see schema code below):   create a like_xx view on table tab_xx as select *.   create an INSTEAD OF INSERT trigger on like_xx.   in the trigger:
    insert row into the original table
    if it fails (raises exception):

      count the records with 100% identical column values.
      if exactly 1 record exists:
         it is a true duplicate. ignore the exception and continue.
      else -- if 0 records exist:
         this is a true exception, re-raise the exception

The instead of trigger fails in the 'select count(*) from' statement. If you look at the debug log below, you'll see that the debug statement behind the select statement is not reached as it should in the buggy session.

How should I go on debugging this error?

Thank you.

## -------- log out put for a buggy session (WITH error)
# first insert is OK
## second raises the bug. 
# insert into like_xx(ID_TAB_XX, DATA) VALUES(1, 'XX'); 
15870 - 2003-03-14 134125(           SCHEMA_X.INS_TRG_XX    4) pre-insert: id:"1" data:"XX"
15870 - 2003-03-14 134125(           SCHEMA_X.INS_TRG_XX   88) maybe DUP_VAL_ON_INDEX

# raises an ORA-6512, see exception stack below

## -- there exception stack we see. 
ORA-06512: at "SCHEMA_X.INS_TRG_XX", line 18
ORA-00001: unique constraint (SCHEMA_X.PK_TAB_XX) violated
ORA-04088: error during execution of trigger 'SCHEMA_X.INS_TRG_XX'

## usage example
-- first insert,

expected to succeed. Number of rows in table is changed. insert into like_xx(ID_TAB_XX, DATA) VALUES(1, 'XX');
second insert, a duplicate, but all fields are equal, so
tolerate it.
expected to succeed. Number of rows in table is unchanged. insert into like_xx(ID_TAB_XX, DATA) VALUES(1, 'XX');
false duplicate, ID is already in table,
FAILURE insert into like_xx(ID_TAB_XX, DATA) VALUES(1, 'YY');

## -- from the pdf documentation
ORA-04088 error during execution of trigger 'string.string' Cause: A runtime error occurred during execution of a trigger. Action: Check the triggers which were involved in the operation.

## ------------------- schema code (edited example)
##  the original contains just more columns instaed of

## one DATA col.
##
create table TAB_XX(

       ID_TAB_XX  NUMBER       NOT NULL,
       DATA       VARCHAR2(10) NOT NULL,
       constraint  AK_TAB_XX_ID   UNIQUE (ID_TAB_XX),
       constraint  PK_TAB_XX_DATA UNIQUE (DATA)
       );

create VIEW like_xx
as select * from TAB_XX;

create or replace trigger INS_TRG_XX
instead of insert
on LIKE_XX
declare -- line 1

c NUMBER;
begin

       utility.debug.fa('pre-insert: id:"%s" data:"%s"',
	      utility.debug.argv(
 	 	to_char(:new.ID_TAB_XX ),
		to_char(:new.DATA)));
       insert into TAB_XX (
         ID_TAB_XX,
         DATA
       )
       values (
         :new.ID_TAB_XX,
	 :new.DATA);
	utility.debug.f('new row');
exception 
        when DUP_VAL_ON_INDEX then begin
          utility.debug.f('maybe DUP_VAL_ON_INDEX');
          -- line 19: this was line 87 before editing in our real trigger
	  select count(*)  into c from TAB_XX xx
	  where
  	       xx.ID_TAB_XX   = :new.ID_TAB_XX
	  and  xx.DATA        = :new.DATA;
          utility.debug.f('past select * count(*)');
          if c = 1 then
 	     -- duplicates where the _pair_ of  ID+DATA  is already in DB
             -- are OK
             -- We just ignore the second INSERT command and report SUCCESS.
	     utility.debug.f('ignored re-insert');
	     null;
	  else 
             -- c should be 0 here, because _either_ ID or DATA are
             -- duplicates but not in the same row! 
             -- someone tried to insert a different DATA set under the
             -- same ID
             -- let them know, it was wrong,
 	     utility.debug.f('real DUP_VAL_ON_INDEX');
             raise ;
	  end if;
       end;
       when others then begin
            utility.debug.f('other error');
            raise;
      end;

end;
/
## -------- log out put for a valid session (WITHOUT the error)

15870 - 2003-03-14 134108(           SCHEMA_X.INS_TRG_XX    4) pre-insert: id:"1" data:"XX"
15870 - 2003-03-14 134108(           SCHEMA_X.INS_TRG_XX   15) new row

15870 - 2003-03-14 134125(           SCHEMA_X.INS_TRG_XX    4) pre-insert: id:"1" data:"XX"
15870 - 2003-03-14 134125(           SCHEMA_X.INS_TRG_XX   18) maybe DUP_VAL_ON_INDEX
15870 - 2003-03-14 134125(           SCHEMA_X.INS_TRG_XX   24) past select * count(*)
15870 - 2003-03-14 134125(           SCHEMA_X.INS_TRG_XX   29) ignored re-insert

15870 - 2003-03-14 134139(           SCHEMA_X.INS_TRG_XX    4) pre-insert: id:"1" data:"YY"
15870 - 2003-03-14 134139(           SCHEMA_X.INS_TRG_XX   18) maybe DUP_VAL_ON_INDEX
15870 - 2003-03-14 134139(           SCHEMA_X.INS_TRG_XX   24) past select * count(*)
15870 - 2003-03-14 134139(           SCHEMA_X.INS_TRG_XX   37) real DUP_VAL_ON_INDEX



-- 

Volker Apelt

Received on Fri Mar 14 2003 - 08:25:55 CST