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 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 at Oracle 11g:
create or replace trigger TRG_DETAIL_TABLE
for insert or delete or update on DETAIL_TABLE
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;