Skip to Main Content
  • Questions
  • Compound trigger unexpected behavior for MERGE and BULK operations

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Vladyslav.

Asked: January 17, 2017 - 3:58 pm UTC

Last updated: January 17, 2017 - 5:49 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi!

I've faced with surprisingly chaotic run of compound trigger parts run for merge and bulk operations.

Initial:
create sequence SEQUENCE_1
start with 1
increment by 1
cache 20
nominvalue 
nomaxvalue 
nocycle
noorder;
/

create table TEST_TRG
(
  a1 NUMBER not null,
  a2 VARCHAR2(100),
  a3 TIMESTAMP(6),
  a4 DATE
);
/

insert into test_trg values(1,'Y',null,null);
insert into test_trg values(2,'N',null,null);
insert into test_trg values(3,'N',null,null);
insert into test_trg values(4,'N',null,null);

CREATE OR REPLACE TRIGGER TEST_COMPOUND
  FOR DELETE OR INSERT OR UPDATE ON TEST_TRG
  COMPOUND TRIGGER
  v_x    number;
  v_y    number;

  --Executed before DML statement  BEFORE STATEMENT IS
  BEFORE STATEMENT IS
  BEGIN
    dbms_output.put_line(' ');
    v_x := DBMS_RANDOM.random();
    dbms_output.put_line('BEFORE STATEMENT. X:'||v_x);
  END BEFORE STATEMENT;

  --Executed before each row change- :NEW, :OLD are available
  BEFORE EACH ROW IS
  BEGIN
    v_y := DBMS_RANDOM.random();
    dbms_output.put('BEFORE EACH ROW. X:'||v_x);
    dbms_output.put_line('  Y:'||v_y);
  END BEFORE EACH ROW;

  --Executed aftereach row change- :NEW, :OLD are available
  AFTER EACH ROW IS
  BEGIN
    dbms_output.put('AFTER EACH ROW. X:'||v_x);
    dbms_output.put_line('  Y:'||v_y);
    dbms_output.put_line(' ');
    NULL;
  END AFTER EACH ROW;

  --Executed after DML statement
  AFTER STATEMENT IS
  BEGIN
    dbms_output.put('AFTER STATEMENT. X:'||v_x);
    dbms_output.put_line('  Y:'||v_y);
    dbms_output.put_line(' ');

  END AFTER STATEMENT;

END;


Merge example:
MERGE INTO test_trg e 
USING (SELECT 3 as a1, 'Y' as a2 
         FROM dual 
       UNION ALL 
       SELECT 4, 'N' 
         FROM dual 
       UNION ALL 
       SELECT 6, 'N' 
         FROM dual 
       UNION ALL 
       SELECT 7, 'N' 
         FROM dual 
       UNION ALL 
       SELECT 8, 'N' 
         FROM dual) h 
ON (e.a1 = h.a1) 
WHEN MATCHED THEN 
  UPDATE SET e.a2 = h.a2 
WHEN NOT MATCHED THEN 
  INSERT 
    (a1, a2) 
  VALUES 
    (h.a1, h.a2);


Result:
BEFORE STATEMENT. X:1026657616
BEFORE STATEMENT. X:-1573756378
BEFORE EACH ROW. X:-1573756378 Y:1551155863
AFTER EACH ROW. X:-1573756378 Y:1551155863
BEFORE EACH ROW. X:-1573756378 Y:-36640373
AFTER EACH ROW. X:-1573756378 Y:-36640373
BEFORE EACH ROW. X: Y:-1297557227
BEFORE EACH ROW. X: Y:-17197619
BEFORE EACH ROW. X: Y:-453600426
AFTER EACH ROW. X: Y:-453600426
AFTER EACH ROW. X: Y:-453600426
AFTER EACH ROW. X: Y:-453600426
AFTER STATEMENT. X: Y:-453600426
AFTER STATEMENT. X:-1573756378 Y:-36640373



Bulk example:
 declare  
  TYPE tt is table of varchar2(1); 
  tb tt; 
  begin 
    tb := tt(); 
    tb.extend(3); 
    tb(1) := 'N'; 
    tb(2) := 'N'; 
    tb(3) := 'N'; 
    forall i in tb.first ..tb.last 
      insert into test_trg values(sequence_1.nextval,tb(i),null,null); 
  end;


Result:
BEFORE STATEMENT. X:680684390
BEFORE EACH ROW. X:680684390 Y:582182012
BEFORE EACH ROW. X:680684390 Y:-2076610854
BEFORE EACH ROW. X:680684390 Y:-9094893
AFTER EACH ROW. X:680684390 Y:-9094893
AFTER EACH ROW. X:680684390 Y:-9094893
AFTER EACH ROW. X:680684390 Y:-9094893
AFTER STATEMENT. X:680684390 Y:-9094893


Questions:

1) In bulk insert (FORALL) (Statement 9) example there are three contiguous BEFORE EACH ROW calls and then three contiguous AFTER EACH ROW calls, not BEFORE-AFTER order. Why?

2) In MERGE example (Statement 8) BEFORE STATEMENT and AFTER STATEMENT both runs twice at the beginning and at the end respectively. Why?

3) For "UPDATE" part (2 first rows) of MERGE example there are correct calls of BEFORE EACH ROW and AFTER EACH ROW parts, but for "INSERT" part I see the similar behaviour as for bulk insert example - three BEFORE EACH ROW calls and then three AFTER EACH ROW parts. Why?

4) In MERGE example I see NULL value for v_x variable for INSERT part, seems like trigger does not store its internal variable value. But this non-null value pop-ups again in AFTER STATEMENT part call. Why?

5) IN MERGE example I see v_y value for AFTER STATEMENTS parts is the same as for the last calls of UPDATE and INSERT parts. Why?




with LiveSQL Test Case:

and Chris said...

1. Oracle Database doesn't guarantee that the "before row" point will fire for all rows before executing the "after row" for any row. Just that for any particular row, the before row section will fire ahead of the after row section for that row.

So you can't rely on the execution order being:

BEFORE ROW 1
AFTER ROW 1
BEFORE ROW 2
AFTER ROW 2
...

or

BEFORE ROW 1
BEFORE ROW 2
..
AFTER ROW 1
AFTER ROW 2
...

2. A merge just a convenient way of combining an insert and update. So the trigger fires once for each operation. This mimics the behaviour of regular triggers:

create sequence SEQUENCE_1;

create table TEST_TRG  ( 
  a1 NUMBER not null,  a2 VARCHAR2(100), 
  a3 TIMESTAMP(6),     a4 DATE 
);

insert into test_trg values(sequence_1.nextval,'Y',null,null);
insert into test_trg values(sequence_1.nextval,'N',null,null);
insert into test_trg values(sequence_1.nextval,'N',null,null);
insert into test_trg values(sequence_1.nextval,'N',null,null);

create or replace trigger test_biu
before insert or update on test_trg
begin
  if inserting then 
    dbms_output.put_line('INSERT BIU'); 
  elsif updating then  
    dbms_output.put_line('UPDATE BIU'); 
  end if;
end;
/

MERGE INTO test_trg e 
USING (SELECT 3 as a1, 'Y' as a2 
         FROM dual 
       UNION ALL 
       SELECT 4, 'N' 
         FROM dual 
       UNION ALL 
       SELECT 6, 'N' 
         FROM dual 
       UNION ALL 
       SELECT 7, 'N' 
         FROM dual 
       UNION ALL 
       SELECT 8, 'N' 
         FROM dual) h 
ON (e.a1 = h.a1) 
WHEN MATCHED THEN 
  UPDATE SET e.a2 = h.a2 
WHEN NOT MATCHED THEN 
  INSERT 
    (a1, a2) 
  VALUES 
    (h.a1, h.a2);

INSERT BIU
UPDATE BIU


3. See 1.

4. There's a known bug with merge and variables. This is why you're seeing the nulls. See MOS notes 1393441.1 & 1638849.1 for more details. In the meantime the workaround is to use package variables.

5. Because that was the last value assigned to the v_y in the insert or update as appropriate.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library