1

Topic: compound trigger different results at removal

I can not understand, what for a feature? There are two tables. On the child hangs compound trigger and auxiliary key with an option cascade. I receive different results at record removal directly from the child table and removal from the parent. The test:

create table parent_tab (par_col1 number, par_col2 date);
alter table parent_tab add constraint parent_tab_PK primary key (par_col1);
create table child_tab (ch_col1 number, ch_col2 date, par_col1_ref number);
alter table child_tab add constraint child_tab_PK primary key (ch_col1);
alter table child_tab add constraint ref_parent_tab foreign key (par_col1_ref) references parent_tab (par_col1) on delete cascade;
CREATE OR REPLACE TRIGGER child_tab_after_delete
for delete on child_tab
compound trigger
vId number: = 0;
after each row is
begin
vId: =:old.ch_col1;
dbms_output.put_line ('  row-wise = ' || vId);
end after each row;
after statement is
begin
dbms_output.put_line ('  Global = ' || vId);
end after statement;
end child_tab_after_delete;

We fill with the data

begin
insert into parent_tab values (1, to_date (' 1/1/2018 ', ' dd.mm.yyyy '));
insert into parent_tab values (2, to_date (' 1/1/2018 ', ' dd.mm.yyyy '));
insert into child_tab values (10, to_date (' 1/1/2018 ', ' dd.mm.yyyy '), 1);
insert into child_tab values (20, to_date (' 1/1/2018 ', ' dd.mm.yyyy '), 2);
end;

We delete records:

begin
delete from child_tab where par_col1_ref=2;
rollback;
delete from parent_tab where par_col1=2;
rollback;
end;

Result:
1) at removal from the descendant it is had:

 row-wise = 20
 Global = 20

2) at removal from the parent it is had:

 row-wise = 20
 Global = 0

The version:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

2

Re: compound trigger different results at removal

We smoke to dock:
Statement triggers fired due to DELETE CASCADE and DELETE SET NULL are fired before and after the user DELETE statement, not before and after the individual enforcement statements. This prevents those statement triggers from encountering mutating errors.
.e. At removal from parent for each row the part compound a on child will be fulfilled as well as it is necessary and here statement the part compound a on child will be fulfilled after end of removal from parent and therefore vId = 0.
SY.

3

Re: compound trigger different results at removal

SQL> CREATE OR REPLACE TRIGGER child_tab_after_delete
2 for delete on child_tab
3 compound trigger
4 vId number: = 0;
5 after each row is
6 begin
7 dbms_output.put_line (' Child row level trigger. ');
8 vId: =:old.ch_col1;
9 dbms_output.put_line (' ID row level = ' || vId);
10 end after each row;
11 after statement is
12 begin
13 dbms_output.put_line (' Child statement level trigger. ');
14 dbms_output.put_line (' ID statement level = ' || vId);
15 end after statement;
16 end child_tab_after_delete;
17 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER parent_tab_after_delete
2 for delete on parent_tab
3 compound trigger
4 after each row is
5 begin
6 dbms_output.put_line (' Parent row level trigger. ');
7 end after each row;
8 after statement is
9 begin
10 dbms_output.put_line (' Parent statement level trigger. ');
11 end after statement;
12 end;
13 /
Trigger created.
SQL> begin
2 dbms_output.put_line ('---------- Deleting child---------- ');
3 delete from child_tab where par_col1_ref=2;
4 rollback;
5 dbms_output.put_line ('---------- Deleting parent---------- ');
6 delete from parent_tab where par_col1=2;
7 rollback;
8 end;
9 /
---------- Deleting child----------
Child row level trigger.
ID row level = 20
Child statement level trigger.
ID statement level = 20
---------- Deleting parent----------
Child row level trigger.
ID row level = 20
>>> Parent row level trigger. <<<
Child statement level trigger.
ID statement level = 0
Parent statement level trigger.
PL/SQL procedure successfully completed.
SQL>

SY.

4

Re: compound trigger different results at removal

It yes, but I here cannot pick up an explanation yet, what would be bad if variable value has been saved and used in statement sections of the child trigger in the second example? After all as a matter of fact in it also there is a sense compound the trigger. And so quits that a simple stroke of a pen adding of the cascade removal we can break the logic implemented earlier. There are thoughts?

5

Re: compound trigger different results at removal

That wrote SY - clearly. But IMHO it really is not transparent that at key adding, the existing logic breaks
PS Naryl still here the such: https://asktom.oracle.com/pls/apex/f?p= … 0346769343
There like the same case, also it is written that a bug (there there is note number, but at me access on a metalink is not present to look)

6

Re: compound trigger different results at removal

23t wrote:

And so quits that a simple stroke of a pen adding of the cascade removal we can break the logic implemented earlier. There are thoughts?

There is your logic and there is a logic smile)). The logician of Oracle to bypass to anybody unnecessary "table is mutating". And this logic is documented. So-that base the logic accordingly. If e from the table can be caused through on delete cascade variables used both for each row and statement level it is pushed in a packet:

SQL> create or replace
2 package pkg1
3 is
4 vID number;
5 end;
6 /
Package created.
SQL> CREATE OR REPLACE TRIGGER child_tab_after_delete
2 for delete on child_tab
3 compound trigger
4 - vId number: = 0;
5 after each row is
6 begin
7 dbms_output.put_line (' Child row level trigger. ');
8 pkg1.vId: =:old.ch_col1;
9 dbms_output.put_line (' ID row level = ' || pkg1.vId);
10 end after each row;
11 after statement is
12 begin
13 dbms_output.put_line (' Child statement level trigger. ');
14 dbms_output.put_line (' ID statement level = ' || pkg1.vId);
15 end after statement;
16 end child_tab_after_delete;
17 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER parent_tab_after_delete
2 for delete on parent_tab
3 compound trigger
4 after each row is
5 begin
6 dbms_output.put_line (' Parent row level trigger. ');
7 end after each row;
8 after statement is
9 begin
10 dbms_output.put_line (' Parent statement level trigger. ');
11 end after statement;
12 end;
13 /
Trigger created.
SQL> begin
2 dbms_output.put_line ('---------- Deleting child---------- ');
3 delete from child_tab where par_col1_ref=2;
4 rollback;
5 dbms_output.put_line ('---------- Deleting parent---------- ');
6 delete from parent_tab where par_col1=2;
7 rollback;
8 end;
9 /
---------- Deleting child----------
Child row level trigger.
ID row level = 20
Child statement level trigger.
ID statement level = 20
---------- Deleting parent----------
Child row level trigger.
ID row level = 20
Parent row level trigger.
Child statement level trigger.
==> ID statement level = 20
Parent statement level trigger.
PL/SQL procedure successfully completed.
SQL>

SY.

7

Re: compound trigger different results at removal

SY wrote:

that variables used both for each row and statement level it is pushed in a packet:

Solomon, you forgot, what compound the trigger have been invented just in order to avoid it?

8

Re: compound trigger different results at removal

Elic wrote:

it is passed...
, you forgot, what compound the trigger have been invented just in order to avoid it?

Did not forget. Simply e exceptions and on cascade delete one of them.
SY.

9

Re: compound trigger different results at removal

And generally, on delete cascade/set null is, IMHO, angrily and the  for it I beat sometimes and financially for clarity.
SY.

10

Re: compound trigger different results at removal

SY wrote:

it is passed...
Did not forget. Simply e exceptions and on cascade delete one of them.

Context promised to store within one firing statement.
But at the cascade removal at line and statement-level the child-trigger firing statement, seemingly, differs.
Using operating time SY:
[spoiler]

clear
create table dropme_parent_tab (par_col1 number, par_col2 date);
alter table dropme_parent_tab add constraint dropme_parent_tab_PK primary key (par_col1);
create table dropme_child_tab (ch_col1 number, ch_col2 date, par_col1_ref number);
alter table dropme_child_tab add constraint dropme_child_tab_PK primary key (ch_col1);
alter table dropme_child_tab add constraint ref_dropme_parent_tab foreign key (par_col1_ref) references dropme_parent_tab (par_col1) on delete cascade;
begin
insert into dropme_parent_tab values (1, to_date (' 1/1/2018 ', ' dd.mm.yyyy '));
insert into dropme_parent_tab values (2, to_date (' 1/1/2018 ', ' dd.mm.yyyy '));
insert into dropme_child_tab values (10, to_date (' 1/1/2018 ', ' dd.mm.yyyy '), 1);
insert into dropme_child_tab values (20, to_date (' 1/1/2018 ', ' dd.mm.yyyy '), 2);
-- insert into dropme_child_tab values (30, to_date (' 1/1/2018 ', ' dd.mm.yyyy '), 2);
end;
/
CREATE OR REPLACE TRIGGER dropme_child_tab_after_delete
for delete on dropme_child_tab
compound trigger
vId number: = 0;
after each row is
begin
dbms_output.put_line (' Child row level trigger. ');
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);
for r in (select status, curno, flag, child_handle from v$sql_cursor where status = ' CURBOUND ')
loop
for i in (select substr (sql_text, 1,40) l_text from v$sql where child_address = r.child_handle)
loop
dbms_output.put_line (r.status || ' * ' || r.curno || ' * ' || r.flag || ' ====> ' || i.l_text);
end loop;
end loop;
end after each row;
after statement is
begin
dbms_output.put_line (' Child statement level trigger. ');
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);
for r in (select status, curno, flag, child_handle from v$sql_cursor where status = ' CURBOUND ')
loop
for i in (select substr (sql_text, 1,40) l_text from v$sql where child_address = r.child_handle)
loop
dbms_output.put_line (r.status || ' * ' || r.curno || ' * ' || r.flag || ' ====> ' || i.l_text);
end loop;
end loop;
end after statement;
end dropme_child_tab_after_delete;
/
sho err
CREATE OR REPLACE TRIGGER dropme_parent_tab_after_delete
for delete on dropme_parent_tab
compound trigger
after each row is
begin
dbms_output.put_line (' Parent row level trigger. ');
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);
for r in (select status, curno, flag, child_handle from v$sql_cursor where status = ' CURBOUND ')
loop
for i in (select substr (sql_text, 1,40) l_text from v$sql where child_address = r.child_handle)
loop
dbms_output.put_line (r.status || ' * ' || r.curno || ' * ' || r.flag || ' ====> ' || i.l_text);
end loop;
end loop;
end after each row;
after statement is
begin
dbms_output.put_line (' Parent statement level trigger. ');
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);
for r in (select status, curno, flag, child_handle from v$sql_cursor where status = ' CURBOUND ')
loop
for i in (select substr (sql_text, 1,40) l_text from v$sql where child_address = r.child_handle)
loop
dbms_output.put_line (r.status || ' * ' || r.curno || ' * ' || r.flag || ' ====> ' || i.l_text);
end loop;
end loop;
end after statement;
end;
/
sho err
set serveroutput on size 10000
begin
dbms_output.put_line ('------------------------------------ ');
dbms_output.put_line ('---------- Deleting child---------- ');
dbms_output.put_line ('------------------------------------ ');
delete from dropme_child_tab where par_col1_ref=2;
rollback;
dbms_output.put_line ('------------------------------------ ');
dbms_output.put_line ('---------- Deleting parent---------- ');
dbms_output.put_line ('------------------------------------ ');
delete from dropme_parent_tab where par_col1=2;
rollback;
end;
/
drop table dropme_child_tab purge;
drop table dropme_parent_tab purge;

[/spoiler]
Found that:

------------------------------------
---------- Deleting>>> parent <<<----------
------------------------------------
==> Child row level trigger.
----- PL/SQL Call Stack----
object line object
handle number name
0xfef1e2060 6 DROPME_CHILD_TAB_AFTER_DELETE
0x10aaf543c8 10 anonymous block
...
==> CURBOUND*7*10317 ====> delete from "DROPME_CHILD_TAB" - Here it, delete on child.
CURBOUND*8*205 ====> DELETE FROM DROPME_PARENT_TAB WHERE PAR_
...
==> Parent row level trigger.
----- PL/SQL Call Stack----
object line object
handle number name
0x10ff272318 5 DROPME_PARENT_TAB_AFTER_DELETE
0x10aaf543c8 10 anonymous block
...
CURBOUND*8*205 ====> DELETE FROM DROPME_PARENT_TAB WHERE PAR_
...
==> Child>>> statement <<<level trigger.
----- PL/SQL Call Stack----
object line object
handle number name
0xfef1e2060 18 DROPME_CHILD_TAB_AFTER_DELETE
0x10aaf543c8 10 anonymous block
...
-- And here I do not see the cursor on child_tab
==> CURBOUND*8*205 ====> DELETE FROM DROPME _>>> PARENT <<<_TAB WHERE PAR_
...
==> Parent statement level trigger.
----- PL/SQL Call Stack----
object line object
handle number name
0x10ff272318 17 DROPME_PARENT_TAB_AFTER_DELETE
0x10aaf543c8 10 anonymous block
...
CURBOUND*8*205 ====> DELETE FROM DROPME_PARENT_TAB WHERE PAR_
...
PL/SQL procedure successfully completed

Probably, there is no cursor - there is no also a context connected with firing statement of the line child-trigger.

11

Re: compound trigger different results at removal

[quote =-2] the Given phrase from docks does not explain influence  a state of a packet of the trigger on appearance mutating errors.

Elic a on grandmother's e struggle against a mutation where for each row collects PK/ROWID in a package collection and statement level climbs with them in the table. Compound the trigger allows to hold a collection in the trigger thereby avoiding costs of type of initialization of a collection. However the execution order on cascade delete/set null tears compound the trigger on statement level and for each row each of which is fulfilled in own context.  e if compound the trigger was fulfilled something like a with serially_reusable pragma. But the call serially_reusable from SQL is forbidden.
SY.