Skip to Main Content
  • Questions
  • Compound Trigger and Global Variables

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mario.

Asked: November 04, 2015 - 11:34 am UTC

Last updated: October 08, 2018 - 1:04 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi!

To avoid mutating exception, i´m using a compound trigger, filling a array and then i intend to loop through this array e do my thing.
The problem is that the global variable loses it contents when I enter "after statement"
if (and only if) i delete from a parent table (the detail table has a fk with delete cascade).

The code below prints:
    delete from my table_detail where parent_id = 1 and id in (1, 2);  
    AFTER EACH ROW 1  
    AFTER EACH ROW 2  
    AFTER STATEMENT 2  


But this code prints:
    delete from my table_master where parent_id = 1;  
    AFTER EACH ROW 1  
    AFTER EACH ROW 2  
    AFTER STATEMENT 0  


What am I doing wrong?

The Trigger:

    create or replace trigger TRG_DETAIL_TABLE  
    for insert or delete or update on table_detail
    compound trigger  

        type rpar is record (  
            id       number,  
            operacao varchar2(1)  
        );  
        type tpar is table of rpar;  
        vpar tpar := tpar();  

        before statement is  
        begin  
            null;  
        end before statement;  

        before each row is  
        begin  
            null;  
        end before each row;  

        after each row is  
        begin  
            vpar.extend;  
            vpar(vpar.last).id       := nvl(:new.id,:old.id);  
            vpar(vpar.last).operacao := case when inserting then 'I' when deleting then 'E' else 'A' end;  
            Dbms_Output.Put_Line('AFTER EACH ROW ' || vpar.count);  
        end after each row;  

        after statement is  
        begin  
            Dbms_Output.Put_Line('AFTER STATEMENT ' || vpar.count);  
        end after statement;  
    end;  

and Chris said...

There is a known bug related to this (MOS note 1638849.1) . The workaround is to place the global variable in a separate package:

drop table table_detail purge;
drop table table_master purge;
  
create table table_master (
  parent_id integer primary key
);

create table table_detail (
  parent_id integer 
    references table_master(parent_id) 
    on delete cascade, 
  id integer primary key
);

insert into table_master values (1);
insert into table_detail values (1, 1);
insert into table_detail values (1, 2);

commit;

create or replace trigger TRG_DETAIL_TABLE  
for insert or delete or update on table_detail
compound trigger  

  type rpar is record (  
      id       number,  
      operacao varchar2(1)  
  );  
  type tpar is table of rpar;  
  vpar tpar := tpar();  

  before statement is  
  begin  
      null;  
  end before statement;  

  before each row is  
  begin  
      null;  
  end before each row;  

  after each row is  
  begin  
      vpar.extend;  
      vpar(vpar.last).id       := nvl(:new.id,:old.id);  
      vpar(vpar.last).operacao := 
        case when inserting then 'I' 
             when deleting then 'E' 
             else 'A' end;  
      Dbms_Output.Put_Line('AFTER EACH ROW ' || vpar.count);  
  end after each row;  

  after statement is  
  begin  
      Dbms_Output.Put_Line('AFTER STATEMENT ' || vpar.count);  
  end after statement;  
end; 
/

set serveroutput on 
delete from table_master where parent_id = 1;

1 row deleted.

AFTER EACH ROW 1
AFTER EACH ROW 2
AFTER STATEMENT 0

rollback;

create or replace package pkg as
  type rpar is record (  
      id       number,  
      operacao varchar2(1)  
  );  
  type tpar is table of rpar;  
  vpar tpar := tpar();  
end pkg;
/
show err

create or replace trigger TRG_DETAIL_TABLE  
for insert or delete or update on table_detail
compound trigger  

  before statement is  
  begin  
      null;  
  end before statement;  

  before each row is  
  begin  
      null;  
  end before each row;  

  after each row is  
  begin  
      pkg.vpar.extend;  
      pkg.vpar(pkg.vpar.last).id       := nvl(:new.id,:old.id);  
      pkg.vpar(pkg.vpar.last).operacao := 
         case when inserting then 'I' 
              when deleting then 'E' 
              else 'A' end;  
      Dbms_Output.Put_Line('AFTER EACH ROW ' || pkg.vpar.count);  
  end after each row;  

  after statement is  
  begin  
      Dbms_Output.Put_Line('AFTER STATEMENT ' || pkg.vpar.count);  
  end after statement;  
end;  
/
sho err

delete from table_master where parent_id = 1;

1 row deleted.

AFTER EACH ROW 1
AFTER EACH ROW 2
AFTER STATEMENT 2

Rating

  (4 ratings)

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

Comments

Thanks!

Mario, November 04, 2015 - 5:36 pm UTC

Thanks!


A reader, September 13, 2016 - 12:05 pm UTC

Saved my day!

Had a similar issue like this! Package variable saved the day!

still issue in 12C?

A reader, October 06, 2018 - 6:58 pm UTC

is this fixed in 12.1 ?
Connor McDonald
October 07, 2018 - 2:08 am UTC

This is from 18.3

SQL> create or replace trigger TRG_DETAIL_TABLE
  2  for insert or delete or update on table_detail
  3  compound trigger
  4
  5    type rpar is record (
  6        id       number,
  7        operacao varchar2(1)
  8    );
  9    type tpar is table of rpar;
 10    vpar tpar := tpar();
 11
 12    before statement is
 13    begin
 14        null;
 15    end before statement;
 16
 17    before each row is
 18    begin
 19        null;
 20    end before each row;
 21
 22    after each row is
 23    begin
 24        vpar.extend;
 25        vpar(vpar.last).id       := nvl(:new.id,:old.id);
 26        vpar(vpar.last).operacao :=
 27          case when inserting then 'I'
 28               when deleting then 'E'
 29               else 'A' end;
 30        Dbms_Output.Put_Line('AFTER EACH ROW ' || vpar.count);
 31    end after each row;
 32
 33    after statement is
 34    begin
 35        Dbms_Output.Put_Line('AFTER STATEMENT ' || vpar.count);
 36    end after statement;
 37  end;
 38  /

Trigger created.

SQL>
SQL> set serveroutput on
SQL> delete from table_master where parent_id = 1;
AFTER EACH ROW 1
AFTER EACH ROW 2
AFTER STATEMENT 0

1 row deleted.

Is it fixed in 12.1?

A reader, October 07, 2018 - 2:11 pm UTC


Connor McDonald
October 08, 2018 - 1:04 am UTC

I think you can pretty safely assume that if its not fixed in 18, its not fixed in 12.1

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