This is part 13 of a post series about Oracle Edition-Based Redefinition.
Visit the index page for all the parts of the series
Introduction
As I wrote in Part 7: Editioning Views:
Editioning views are the interface between the application and the tables
This is one of the most important rules when using EBR.
Code objects should never refer to tables.
Instead of referring to a table, code objects should refer to the corresponding editioning view – the editioning view that covers that table.
Code objects include packages, procedures, functions, views, regular (i.e., not cross-edition) triggers, etc. You can see the full list in Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned.
Cross-edition triggers are excluded from this list, as they are not really part of our code base – they are temporary objects that their lifespan is limited to the upgrade timeframe (as soon as no client uses the pre-upgrade edition anymore, they are dropped).
And, by definition, they should refer to the base tables.
However, there is one case (that I can think of) of a code object that has to refer to the base table, as referring to the editioning view is not enough.
The ON DELETE Clause
Let’s say we have two tables with a foreign key constraint between them. The foreign key is defined with the ON DELETE clause (either CASCADE or SET NULL), which means that whenever a record is deleted from the parent table, the corresponding child records are automatically deleted (if the ON DELETE CASCADE option is used) or updated (if the ON DELETE SET NULL is used).
SQL> create table parents$0 (
2 id integer not null constraint parents$0_pk primary key
3 );
Table created.
SQL> create editioning view parents as
2 select id from parents$0;
View created.
SQL> create table children$0 (
2 child_id integer not null constraint children$0_pk primary key,
3 parent_id constraint children$0_fk_parents$0
4 references parents$0 (id)
5 on delete cascade
6 );
Table created.
SQL> create index ix_children$0_parent_id on children$0 (parent_id);
Index created.
SQL> create editioning view children as
2 select child_id,parent_id from children$0;
View created.
SQL> insert into parents (id)
2 select rownum from dual connect by level<=5;
5 rows created.
SQL> insert into children (child_id,parent_id)
2 select p.id*100+1,p.id from parents p;
5 rows created.
SQL> insert into children (child_id,parent_id)
2 select p.id*100+2,p.id from parents p;
5 rows created.
SQL> commit;
Commit complete.
SQL> select * from children order by 1,2;
CHILD_ID PARENT_ID
---------- ----------
101 1
102 1
201 2
202 2
301 3
302 3
401 4
402 4
501 5
502 5
10 rows selected.
Under The Hood
Let’s see what happens under the hood (by turning SQL trace on and examining the trace file) when we delete a parent record:
SQL> exec dbms_monitor.session_trace_enable
PL/SQL procedure successfully completed.
SQL> delete parents where id=1;
1 row deleted.
SQL> exec dbms_monitor.session_trace_disable
PL/SQL procedure successfully completed.
Note that we delete from PARENTS – the editioning view. We never perform DML statements on PARENTS$0 – the table itself.
Let’s look at the trace file:
===================== PARSING IN CURSOR #1898635225640 len=25 dep=0 uid=110 oct=7 lid=110 tim=1300489027104 hv=3764805104 ad='7ff80d19f688' sqlid='cbpm02rh6crgh' delete parents where id=1 END OF STMT PARSE #1898635225640:c=0,e=1951,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=3164448647,tim=1300489027104 ===================== PARSING IN CURSOR #1898736853800 len=54 dep=1 uid=0 oct=7 lid=0 tim=1300489027667 hv=3090565272 ad='7ff80d367f48' sqlid='269qzkfw3ck4s' delete from "TST"."CHILDREN$0" where "PARENT_ID" = :1 END OF STMT PARSE #1898736853800:c=0,e=294,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1300489027667 EXEC #1898736853800:c=0,e=608,p=0,cr=1,cu=10,mis=1,r=2,dep=1,og=1,plh=615902739,tim=1300489028334 STAT #1898736853800 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE CHILDREN$0 (cr=1 pr=0 pw=0 str=1 time=72 us)' STAT #1898736853800 id=2 cnt=2 pid=1 pos=1 obj=78411 op='INDEX RANGE SCAN IX_CHILDREN$0_PARENT_ID (cr=1 pr=0 pw=0 str=1 time=9 us cost=1 size=26 card=1)' CLOSE #1898736853800:c=0,e=0,dep=1,type=3,tim=1300489028483 EXEC #1898635225640:c=0,e=1351,p=0,cr=2,cu=16,mis=0,r=1,dep=0,og=1,plh=3164448647,tim=1300489028499 STAT #1898635225640 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE PARENTS$0 (cr=2 pr=0 pw=0 str=1 time=1309 us)' STAT #1898635225640 id=2 cnt=1 pid=1 pos=1 obj=78408 op='INDEX UNIQUE SCAN PARENTS$0_PK (cr=1 pr=0 pw=0 str=1 time=13 us cost=1 size=13 card=1)' WAIT #1898635225640: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1300489028581 WAIT #1898635225640: nam='SQL*Net message from client' ela= 571 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1300489029166 CLOSE #1898635225640:c=0,e=5,dep=0,type=0,tim=1300489032159 =====================
We can obviously see the DELETE statement that we explicitly executed (note that dep=0 – it means it’s our top-level statement)
PARSING IN CURSOR #1898635225640 len=25 dep=0 uid=110 oct=7 lid=110 tim=1300489027104 hv=3764805104 ad='7ff80d19f688' sqlid='cbpm02rh6crgh'
delete parents where id=1
But there is another DELETE statement, that was executed after our statement had been parsed and before it was executed – a statement that was added implicitly due to the ON DELETE CASCADE rule.
And since this rule is part of the definition of the Foreign Key constraint, which is defined on the table itself and is not editionable, then this implicit DELETE statement is from the CHILDREN$0 table itself (note that dep=1 – it means it’s a recursive statement, done by Oracle as a result of our statement)
PARSING IN CURSOR #1898736853800 len=54 dep=1 uid=0 oct=7 lid=0 tim=1300489027667 hv=3090565272 ad='7ff80d367f48' sqlid='269qzkfw3ck4s'
delete from "TST"."CHILDREN$0" where "PARENT_ID" = :1
Now, this is perfectly fine. There is no problem with this implementation per se.
So, What’s The Problem?
The problem arises if we want to create a trigger on the Children entity.
More accurately, if:
The foreign key on CHILDREN$0 is defined with ON DELETE CASCADE, and we want to create a trigger that will be fired upon deletion of children records
Or
The foreign key on CHILDREN$0 is defined with ON DELETE SET NULL, and we want to create a trigger that will be fired upon updating of the referencing key
Based on our EBR rule, the trigger should be defined on the CHILDREN editioning view, and this will work fine for explicit DELETE FROM CHILDREN statements. But such trigger will not be fired as a result of the implicit DELETE FROM CHILDREN$0 statements.
Conclusion
Therefore, if we have a foreign key constraint with the ON DELETE rule, and we must have a trigger on the child entity that should be fired when the ON DELETE rule kicks in, then, unfortunately, we have to create the trigger on the table itself and not on the editioning view.
And then we need to think carefully what to do during upgrades that may affect these table and trigger.
Fortunately, such a combination of ON DELETE and a related trigger is (I believe and hope) quite rare.