SQL & PL/SQL

Dear all,

with Oracle 11g XE, I need to implement a mechanism in order to trigger the deletion of parent table tuples. If a tuple from table parent is deleted, with the FK mechanism and cascading delete, corresponding child tuples are deleted, and for each of them, I need to be able to trigger the deletion of related parent tuple(s).

I have the two following tables and test data:

create table parent (
"parent_id" number(*,0),
"val" varchar2(4000 byte) not null enable,
"parent_id_ref" number(*,0) not null enable,
"version_id" number(*,0) not null enable,
primary key ("parent_id") ,
constraint "fk_parent" foreign key ("parent_id_ref") references parent ("parent_id") on delete cascade enable
);

create table child (
"child_id" number(*,0) not null enable,
"parent_target" number(*,0) not null enable,
"parent_source" number(*,0) not null enable,
"version_id" number(*,0) not null enable,
constraint "relationship_pk" primary key ("child_id") ,
constraint "fk_source" foreign key ("parent_source") references parent ("parent_id") on delete cascade enable,
constraint "fk_target" foreign key ("parent_target") references parent ("parent_id") on delete cascade enable
);


insert into parent (parent_id,val,parent_id_ref,version_id) values (0,'root',0,0);
insert into parent (parent_id,val,parent_id_ref,version_id) values (1,'role',0,0);
insert into parent (parent_id,val,parent_id_ref,version_id) values (3,'class',0,0);
insert into parent (parent_id,val,parent_id_ref,version_id) values (9,'class_role',0,0);
insert into parent (parent_id,val,parent_id_ref,version_id) values (11,'method',0,0);
insert into parent (parent_id,val,parent_id_ref,version_id) values (16,'class_role_method',0,0);
insert into parent (parent_id,val,parent_id_ref,version_id) values (18,'is',0,0);
insert into parent (parent_id,val,pparent_id_ref,version_id) values (19,'is_role',0,0);
insert into parent (parent_id,val,parent_id_ref,version_id) values (20,'class_is',0,0);
insert into parent (parent_id,val,parent_id_ref,version_id) values (24,'class_role_is',0,0);
insert into parent (parent_id,val,parent_id_ref,version_id) values (29,'class_is_method',0,0);
insert into parent (parent_id,val,parent_id_ref,version_id) values (31,'class_role_method_is',0,0);


insert into child (child_id,parent_target,parent_source,version_id) values (6,3,9,0);
insert into child (child_id,parent_target,parent_source,version_id) values (9,3,11,0);
insert into child (child_id,parent_target,parent_source,version_id) values (18,9,16,0);
insert into child (child_id,parent_target,parent_source,version_id) values (19,11,16,0);
insert into child (child_id,parent_target,parent_source,version_id) values (22,1,19,0);
insert into child (child_id,parent_target,parent_source,version_id) values (23,18,19,0);
insert into child (child_id,parent_target,parent_source,version_id) values (24,18,20,0);
insert into child (child_id,parent_target,parent_source,version_id) values (25,3,20,0);
insert into child (child_id,parent_target,parent_source,version_id) values (34,19,24,0);
insert into child (child_id,parent_target,parent_source,version_id) values (35,20,24,0);
insert into child (child_id,parent_target,parent_source,version_id) values (36,9,24,0);
insert into child (child_id,parent_target,parent_source,version_id) values (47,20,29,0);
insert into child (child_id,parent_target,parent_source,version_id) values (48,11,29,0);
insert into child (child_id,parent_target,parent_source,version_id) values (52,24,31,0);
insert into child (child_id,parent_target,parent_source,version_id) values (53,16,31,0);
insert into child (child_id,parent_target,parent_source,version_id) values (54,29,31,0);
insert into child (child_id,parent_target,parent_source,version_id) values (95,1,9,0);


I have created a package and three triggers:

-- package
create or replace
package delete_child_pkg
as
type array is table of parent%rowtype index by pls_integer;
oldvals array;
emptyx array;
end;

-- first trigger
create or replace
trigger a
before delete on child
begin
delete_child_pkg.oldvals := delete_child_pkg.emptyx;
end;

-- second trigger
create or replace
trigger b
before delete on child
for each row
declare
j number default delete_child_pkg.oldvals.count+1;
begin
delete_child_pkg.oldvals(j).parent_id := :old.parent_source;
end;

--third trigger
create or replace
trigger c
after delete on child
declare
to_delete parent.parent_id%type;
begin
for i in 1 .. delete_child_pkg.oldvals.count loop
if (delete_child_pkg.oldvals.exists(i)) then
delete from parent where parent.parent_id=delete_child_pkg.oldvals(i).parent_id;
end if;
end loop;
end;


My problem is that not all related parent tuples are deleted. With the deletion of parent_id=3, only parent_id=9 is deleted.

I cannot figure out where the bug is.. Is the cascading delete executed before or after the triggers? What am I missing?

Many thanks in advance for your comments,

Adnavv

Edited by: 921444 on 16 mars 2012 09:02