create table MY_CENTRAL_TABLE (
PK number(19) primary key,
DESCRIPTION varchar2(50 char)
)
create table MY_AUXILIARY_TABLE_1 (
AUXILIARY_KEY number(19) primary key,
DESCRIPTION_1 varchar2(50 char),
foreign key (AUXILIARY_KEY) references MY_CENTRAL_TABLE (PK)
on delete cascade
)
create or replace trigger MY_AUXILIARY_TABLE_1_TD before delete on MY_AUXILIARY_TABLE_1 for each row
begin
delete from MY_CENTRAL_TABLE where PK = :old.AUXILIARY_KEY;
--exception
-- when others then
-- if SQLCODE = -4091 then
-- DBMS_OUTPUT.put_line (SQLERRM);
-- end if;
end;
create table MY_AUXILIARY_TABLE_2 (
AUXILIARY_KEY number(19) primary key,
DESCRIPTION_2 varchar2(50 char),
foreign key (AUXILIARY_KEY) references MY_CENTRAL_TABLE (PK)
on delete cascade
)
create or replace trigger MY_AUXILIARY_TABLE_2_TD before delete on MY_AUXILIARY_TABLE_2 for each row
begin
delete from MY_CENTRAL_TABLE where PK = :old.AUXILIARY_KEY;
--exception
-- when others then
-- if SQLCODE = -4091 then
-- DBMS_OUTPUT.put_line (SQLERRM);
-- end if;
end;
create table MY_SECONDARY_AUXILIARY_TABLE_11 (
SECONDARY_AUXILIARY_KEY number(19) primary key,
AUXILIARY_KEY number(19),
DESCRIPTION_1 varchar2(50 char),
foreign key (AUXILIARY_KEY) references MY_AUXILIARY_TABLE_1 (AUXILIARY_KEY)
on delete cascade
)
create table MY_SECONDARY_AUXILIARY_TABLE_12 (
SECONDARY_AUXILIARY_KEY number(19) primary key,
AUXILIARY_KEY number(19),
DESCRIPTION_2 varchar2(50 char),
foreign key (AUXILIARY_KEY) references MY_AUXILIARY_TABLE_2 (AUXILIARY_KEY)
on delete cascade
)
insert into MY_CENTRAL_TABLE
values (1, 'MY_CENTRAL_TABLE row 1')
insert into MY_CENTRAL_TABLE
values (2, 'MY_CENTRAL_TABLE row 2')
insert into MY_AUXILIARY_TABLE_1
values (1, 'MY_AUXILIARY_TABLE_1 row 1')
insert into MY_AUXILIARY_TABLE_2
values (2, 'MY_AUXILIARY_TABLE_2 row 1')
insert into MY_SECONDARY_AUXILIARY_TABLE_11
values (101, 1, 'MY_SECONDARY_AUXILIARY_TABLE_11 row 1')
insert into MY_SECONDARY_AUXILIARY_TABLE_11
values (102, 1, 'MY_SECONDARY_AUXILIARY_TABLE_11 row 2')
insert into MY_SECONDARY_AUXILIARY_TABLE_12
values (101, 2,'MY_SECONDARY_AUXILIARY_TABLE_12 row 1')
insert into MY_SECONDARY_AUXILIARY_TABLE_12
values (102, 2, 'MY_SECONDARY_AUXILIARY_TABLE_12 row 2')
commit
select * from (select count(*) as T0 from MY_CENTRAL_TABLE)
cross join (select count(*) as T01 from MY_AUXILIARY_TABLE_1)
cross join (select count(*) as T11 from MY_SECONDARY_AUXILIARY_TABLE_11)
cross join (select count(*) as T02 from MY_AUXILIARY_TABLE_2)
cross join (select count(*) as T12 from MY_SECONDARY_AUXILIARY_TABLE_12)
delete MY_CENTRAL_TABLE
commit
select * from (select count(*) as T0 from MY_CENTRAL_TABLE)
cross join (select count(*) as T01 from MY_AUXILIARY_TABLE_1)
cross join (select count(*) as T11 from MY_SECONDARY_AUXILIARY_TABLE_11)
cross join (select count(*) as T02 from MY_AUXILIARY_TABLE_2)
cross join (select count(*) as T12 from MY_SECONDARY_AUXILIARY_TABLE_12)
insert into MY_CENTRAL_TABLE
values (1, 'MY_CENTRAL_TABLE row 1')
insert into MY_CENTRAL_TABLE
values (2, 'MY_CENTRAL_TABLE row 2')
insert into MY_AUXILIARY_TABLE_1
values (1, 'MY_AUXILIARY_TABLE_1 row 1')
insert into MY_AUXILIARY_TABLE_2
values (2, 'MY_AUXILIARY_TABLE_2 row 1')
insert into MY_SECONDARY_AUXILIARY_TABLE_11
values (101, 1, 'MY_SECONDARY_AUXILIARY_TABLE_11 row 1')
insert into MY_SECONDARY_AUXILIARY_TABLE_11
values (102, 1, 'MY_SECONDARY_AUXILIARY_TABLE_11 row 2')
insert into MY_SECONDARY_AUXILIARY_TABLE_12
values (101, 2,'MY_SECONDARY_AUXILIARY_TABLE_12 row 1')
insert into MY_SECONDARY_AUXILIARY_TABLE_12
values (102, 2, 'MY_SECONDARY_AUXILIARY_TABLE_12 row 2')
commit
select * from (select count(*) as T0 from MY_CENTRAL_TABLE)
cross join (select count(*) as T01 from MY_AUXILIARY_TABLE_1)
cross join (select count(*) as T11 from MY_SECONDARY_AUXILIARY_TABLE_11)
cross join (select count(*) as T02 from MY_AUXILIARY_TABLE_2)
cross join (select count(*) as T12 from MY_SECONDARY_AUXILIARY_TABLE_12)
delete MY_AUXILIARY_TABLE_1
commit
delete MY_AUXILIARY_TABLE_2
commit
select * from (select count(*) as T0 from MY_CENTRAL_TABLE)
cross join (select count(*) as T01 from MY_AUXILIARY_TABLE_1)
cross join (select count(*) as T11 from MY_SECONDARY_AUXILIARY_TABLE_11)
cross join (select count(*) as T02 from MY_AUXILIARY_TABLE_2)
cross join (select count(*) as T12 from MY_SECONDARY_AUXILIARY_TABLE_12)
drop table MY_SECONDARY_AUXILIARY_TABLE_11
drop table MY_SECONDARY_AUXILIARY_TABLE_12
drop table MY_AUXILIARY_TABLE_1
drop table MY_AUXILIARY_TABLE_2
drop table MY_CENTRAL_TABLE