Re: Problem with before insert triggers when passing column values as literals

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/05
Message-ID: <8cffss$q06$1@nnrp1.deja.com>#1/1

In article <38eb1370.0_at_juno.wiesbaden.netsurf.de>,   Dirk Sudheimer <sudheimer_at_gmx.de> wrote:
> We encountered some really disturbing effect using
> a before insert trigger for each row when testing
> an existing application on 8.1.5 (SUN Solaris 2.6)
> that worked well under 7.3.
>
> Upon inserting multiple rows only the first row
> is properly processed.
>
> Upon further investigation the problem seems to be
> related to passing column values as literals.
>
> Oracle Support told us that this bug should be
> fixed with 8.1.6. Is someone able to confirm this
> statement?
>
> thanx in advance
>
> D. Sudheimer
>

fix confirmed:

SQL>select * from v$version;

BANNER

---

Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for Solaris: Version 8.1.6.0.0 - Production NLSRTL Version 3.4.0.0.0 - Production

SQL>-- trigger is fired with historynr passed as constant = -2 SQL>insert into TRIGGERT (historynr,id)
  2 (select -2, id from TRIGGERTS) ;

3 rows created.

SQL>
SQL>-- wrong output since trigger only handles the first row
SQL>select * from TRIGGERT ;

        ID  HISTORYNR
---------- ----------
        -1         -1
        -2         -1
        -3         -1

SQL>
SQL>DELETE FROM TRIGGERT ; 3 rows deleted.

SQL>
SQL>-- trigger is fired with historynr passed as result of a function. - - (although in this example the function always yields -2 this SQL>-- obviously is hidden from oracle)
SQL>insert into TRIGGERT (historynr,id)
  2 (select DECODE(id, NULL, -42, -2), id from TRIGGERTS) ;

3 rows created.

SQL>
SQL>-- output correct
SQL>select * from TRIGGERT ;

        ID  HISTORYNR
---------- ----------
        -1         -1
        -2         -1
        -3         -1

This appears only to affect 8.1.5, a workaround for you in that release would be:

create or replace package state_pkg
as

    type rowidArray is table of rowid index by binary_integer;     newones rowidArray;
    empty rowidArray;
end;
/

create or replace trigger triggert_BI
before insert on triggert
begin

    state_pkg.newones := state_pkg.empty; end;
/

create or replace trigger triggert_AIFER after insert on triggert for each row
begin

    if ( :new.historynr = -2 )
    then

        state_pkg.newones( state_pkg.newones.count+1 ) := :new.rowid;     end if;
end;
/

create or replace trigger trigger_ai
after insert on triggert
begin

    for i in 1 .. state_pkg.newones.count loop

        update triggert
          set historynr = -1, id = -id
        where rowid = state_pkg.newones(i);

    end loop;
    state_pkg.newones := state_pkg.empty; end;
/

To read more about the approach (its the same as avoiding a mutating table) see http://osi.oracle.com/~tkyte/Mutate/index.html

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Apr 05 2000 - 00:00:00 CDT