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
Following a test script to reproduce the effect:
- Target Table
CREATE TABLE TRIGGERT
(
ID NUMBER(12,0) NOT NULL,
HISTORYNR NUMBER(12,0) NULL
);
- Source Table
CREATE TABLE TRIGGERTS
(
ID NUMBER(12,0) NOT NULL
);
- Fill Source Table
INSERT INTO TRIGGERTS (ID) VALUES (1);
INSERT INTO TRIGGERTS (ID) VALUES (2);
INSERT INTO TRIGGERTS (ID) VALUES (3);
- Create Trigger for Target Table
CREATE OR REPLACE TRIGGER tI_TRIGGERT
BEFORE INSERT ON TRIGGERT FOR EACH ROW
DECLARE
BEGIN
/* When historynr is passed as constant,*/
/* its value is set to -1 on execution of the trigger */
/* for the first row and remains -1 for all successive */
/* rows. So the if branch only is executed for the */
/* first row.*/
IF :new.historynr = -2
THEN
:new.historynr := -1;
:new.id := -:NEW.id;
END IF;
END;
/
- trigger is fired with historynr passed as constant = -2
insert into TRIGGERT (historynr,id)
(select -2, id from TRIGGERTS) ;
- wrong output since trigger only handles the first row
select * from cus.TRIGGERT ;
DELETE FROM cus.TRIGGERT ;
- trigger is fired with historynr passed as result of a function.
- (although in this example the function always yields -2 this
- obviously is hidden from oracle)
insert into TRIGGERT (historynr,id)
(select DECODE(id, NULL, -42, -2), id from TRIGGERTS) ;
- output correct
select * from cus.TRIGGERT ;
Drop table triggert;
Drop table triggerts;
Received on Wed Apr 05 2000 - 00:00:00 CDT
