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

Thomas J. Kyte wrote:
>
> 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.

Hello Thomas,

Did you manufacture this bug as an additional argument for bind variables ?

:-)  

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse