Re: 10.1 / 10.2: different trigger behaviour

> Posting the actual error message and possibly the trigger code would > make it possible for someone to help you.

OK, thanks. Here's the error:

716 rows created.
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CHS.LIBRARYCONNECTOR_FK) violated - parent key not found

The "716 rows" message relates to the COMPONEN table. Here's the trigger:

create or replace trigger COMPONEN_T001 before insert or update of grpname on componen
for each row
declare
libcomp_id varchar2(64);
lib_count number;
libpincontainer_count number;
begin

• See if the librarypincontainer record already exists libcomp_id := trim(:new.componen_id); select count(1) into libpincontainer_count from librarypincontainer where librarypincontainer_id = trim(libcomp_id);

  if inserting then
    if (:new.progress_recid is null) then       select componen_seq.nextval into :new.PROGRESS_RECID from dual;

      :new.classid := ' ';
    else

• Create the librarypincontainer record if it does not exist. if (libpincontainer_count = 0) then insert into librarypincontainer(librarypincontainer_id, componen_id) values (libcomp_id,libcomp_id); end if;

    end if;

    if ((:new.INTENUM is null) or (:new.INTENUM = 0)) then       select intenum_s.nextval into :new.INTENUM from dual;     end if;

  end if;

• Now add each group based record but only if they don't exist. if (upper(:new.grpname) = 'CONNECTOR') then select count(1) into lib_count from libraryconnector where libraryconnector_id = trim(libcomp_id); if (lib_count = 0) then insert into libraryconnector(libraryconnector_id, librarypincontainer_id) values(libcomp_id,libcomp_id); end if; else if (upper(:new.grpname) = 'IDC CONNECTOR') then select count(1) into lib_count from libraryidcconnector where libraryidcconnector_id = trim(libcomp_id); if (lib_count = 0) then insert into libraryidcconnector(libraryidcconnector_id, librarypincontainer_id) values (libcomp_id,libcomp_id); end if; else if (upper(:new.grpname) = 'ULTRASONIC WELD') then select count(1) into lib_count from libraryultrasonicweld where libraryultrasonicweld_id = trim(libcomp_id); if (lib_count = 0) then insert into libraryultrasonicweld(libraryultrasonicweld_id, componen_id) values (libcomp_id,libcomp_id); end if; else if (upper(:new.grpname) = 'BACKSHELL') then select count(1) into lib_count from librarybackshell where librarybackshell_id = trim(libcomp_id); if (lib_count = 0) then insert into librarybackshell(librarybackshell_id, librarypincontainer_id) values (libcomp_id,libcomp_id); end if; end if; end if; end if; end if;

  select colcode into :new.colcode from colour where color_id = :new.color_id;
  select matcode into :new.matcode from material where material_id = :new.material_id;
  select typcode into :new.typcode from comptype where comptype_id = :new.comptype_id;

  :new.u##intepn := upper(trim(:new.intepn));
  :new.u##matihalf := upper(trim(:new.matihalf));
  :new.u##testblok := upper(trim(:new.testblok));
  :new.u##altepn := upper(trim(:new.altepn));
  :new.u##classid := upper(trim(:new.classid));
  :new.u##matcode := upper(trim(:new.matcode));
  :new.u##colcode := upper(trim(:new.colcode));
  :new.u##pnstatus := upper(trim(:new.pnstatus));
  :new.u##typcode := upper(trim(:new.typcode));
  :new.u##grpname := upper(trim(:new.grpname));

• trim may result in null, stop ' ' in columns,
• otherwise they come in as ?? on capH UI if ( :new.matihalf is null ) then
  :new.matihalf := ' ';
  end if; if ( :new.u##matihalf is null ) then
  :new.u##matihalf := ' ';
  end if; if ( :new.testblok is null ) then
  :new.testblok := ' ';
  end if; if ( :new.u##testblok is null ) then
  :new.u##testblok := ' ';
  end if; if ( :new.altepn is null ) then
  :new.altepn := ' ';
  end if; if ( :new.u##altepn is null ) then
  :new.u##altepn := ' ';
  end if; if ( :new.classid is null ) then
  :new.classid := ' ';
  end if; if ( :new.u##classid is null ) then
  :new.u##classid := ' ';
  end if; if ( :new.userf##1 is null ) then
  :new.userf##1 := ' ';
  end if; if ( :new.userf##2 is null ) then
  :new.userf##2 := ' ';
  end if; if ( :new.userf##3 is null ) then
  :new.userf##3 := ' ';
  end if; if ( :new.userf##4 is null ) then
  :new.userf##4 := ' ';
  end if; if ( :new.userf##5 is null ) then
  :new.userf##5 := ' ';
  end if; if ( :new.descript is null ) then
  :new.descript := ' ';
  end if; if ( :new.intespec is null ) then
  :new.intespec := ' ';
  end if; if ( :new.outside_diameter is null ) then
  :new.outside_diameter := 0 ;
  end if; if ( :new.replacedby is null ) then
  :new.replacedby := ' ' ;
  end if; if ( :new.backshell is null ) then
  :new.backshell := ' ' ;
  end if;

end;
/

Of the tables mentioned in the trigger, LIBRARYPINCONTAINER is the child of COMPONEN, and LIBRARYCONNECTOR is the child of LIBRARYPINCONTAINER. The constraint violation that I'm seeing when I populate COMPONEN is because the trigger is trying to create a LIBRARYCONNECTOR but there is no owning LIBRARYPINCONTAINER for it.

> constraints are deferrable on 1, not so on release 2

> your data is not the same; you overlooked something.

I don't think that either of these are possible in the test that I'm doing. I start out with a new user that has no schema whatsoever, and I load a dump file into it, giving me the schema and the data. However, to double-check, I ran a schema comparison utility between the r1 and r2 schemas before running the script, and it reported no differences. It would have picked up any extra / missing / changed objects.

Regards,
Tarby Received on Mon Jan 22 2007 - 05:18:57 CST