My Oracle Support Banner

Before Trigger Behavior Changed After an Upgrade from 10.2 To 11.2 for DML Array Binds (Doc ID 1354634.1)

Last updated on DECEMBER 26, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

After an upgrade to 11.2, a before insert trigger will fire for every row in the array when performing a bulk load of data using DML array bind inserts. It will fire for all rows including rejected rows due to a primary key constraint violation. The new behavior started after a database upgrade to 11.2. With previous versions, the before insert trigger would only fire for each row successfully inserted.

Here is a simple test case scenario that demonstrates the issue:

SQL> CREATE TABLE DATA_TABLE(
2 EVENT_ID INT NOT NULL,
3 EVENT_DEC CHAR(10)
4 );

Table created.

SQL> ALTER TABLE DATA_TABLE
2 ADD PRIMARY KEY (EVENT_ID);

Table altered.

SQL> CREATE TABLE TRIGGER_TABLE(
2 TRIGGER_COUNT INT NOT NULL
3 );

Table created.

SQL> INSERT INTO TRIGGER_TABLE VALUES( 0 );

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CREATE or REPLACE TRIGGER TRIG1
2 BEFORE INSERT ON DATA_TABLE
3 REFERENCING NEW AS newRow
4 FOR EACH ROW
5 BEGIN
6 UPDATE TRIGGER_TABLE SET TRIGGER_COUNT = TRIGGER_COUNT +1;
7 END TRIG1;
8 /

Trigger created.

SQL> select * from trigger_table;

TRIGGER_COUNT
-------------
0

SQL> set serveroutput on
SQL> declare
2 type bulk_data_table is table of data_table%rowtype index by pls_integer;

3 ins_data_c bulk_data_table;
4 begin
5 ins_data_c(1).event_id := 1;
6 ins_data_c(1).event_dec := 'Row1';
7 ins_data_c(2).event_id := 2;
8 ins_data_c(2).event_dec := 'Row2';
9 ins_data_c(3).event_id := 2; -- ORA-00001
10 ins_data_c(3).event_dec := 'RowNew';
11 ins_data_c(4).event_id := 3;
12 ins_data_c(4).event_dec := 'Row4';
13 ins_data_c(5).event_id := 3; -- ORA-00001
14 ins_data_c(5).event_dec := 'Row5';
15 ins_data_c(6).event_id := 4;
16 ins_data_c(6).event_dec := 'Row6';
17
18 begin
19 forall i in indices of ins_data_c
20 insert into data_table values ins_data_c(i);
21 exception
22 when others then
23 dbms_output.put_line(sqlerrm);
24 end;
25 dbms_output.put_line('sql%rowcount says ' || to_char(sql%rowcount) || ' li
nes affected');
26 end;
27 /
ORA-00001: unique constraint (SCOTT.SYS_C009834) violated
sql%rowcount says 2 lines affected

PL/SQL procedure successfully completed.

For 10.2 and earlier versions the trigger count is:

SQL> select * from trigger_table;

TRIGGER_COUNT
-------------
2

For 11.1 or higher the trigger count is:

SQL> select * from trigger_table;

TRIGGER_COUNT
-------------
6

Changes

Upgrade of the database from 10.2 to 11.2.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.