refactions.sql

derby database source code.good for you.

create table emp(empno char(2) not null, mgr char(2), constraint emp primary key(empno),
  constraint manages foreign key(mgr) references emp(empno) on delete cascade);

create table tempemp(empno char(2) , mgr char(2)  , op char(2));

insert into emp values('e1', null);
insert into emp values('e2', 'e1');
insert into emp values('e3', 'e1');
insert into emp values('e4', 'e2');
insert into emp values('e5', 'e4');
insert into emp values('e6', 'e5');
insert into emp values('e7', 'e6');
insert into emp values('e8', 'e7');
insert into emp values('e9', 'e8');


create trigger trig_emp_delete after DELETE on emp
REFERENCING OLD_Table AS deletedrows
for each statement mode db2sql 
insert into tempemp select empno, mgr,  'ad' from deletedrows;

delete from emp where empno = 'e1';

select * from emp;
select * from tempemp;

drop table emp;
drop table tempemp;

-- triggers on a self referencing table

create table emp(empno char(2) not null, mgr char(2), constraint emp primary key(empno),
  constraint manages foreign key(mgr) references emp(empno) on delete cascade);

create table tempemp(empno char(2) , mgr char(2)  , op char(2));

insert into emp values('e1', null);
insert into emp values('e2', 'e1');
insert into emp values('e3', 'e1');
insert into emp values('e4', 'e2');
insert into emp values('e5', 'e4');
insert into emp values('e6', 'e5');
insert into emp values('e7', 'e6');
insert into emp values('e8', 'e7');
insert into emp values('e9', 'e8');


create trigger trig_emp_delete AFTER DELETE on emp
REFERENCING OLD_Table AS deletedrows
for each statement mode db2sql 
insert into tempemp select empno, mgr,  'bd' from deletedrows;

delete from emp where empno = 'e1';

select * from emp;
select * from tempemp;

drop table emp;
drop table tempemp;


--After triggers on a cyclic referential actions
create table t1(a int not null primary key, b int not null unique);
create table t2(x int not null primary key, y int);

insert into t1 values (1, 2);
insert into t1 values (2, 1);
insert into t2 values (1, 2);
insert into t2 values (2, 1);

insert into t1 values (3, 4);
insert into t1 values (4, 3);
insert into t2 values (3, 4);
insert into t2 values (4, 3);

insert into t1 values (6, 7);
insert into t1 values (7, 6);
insert into t2 values (6, 7);
insert into t2 values (7, 6);


alter table t1 add constraint c1 foreign key (b) 
                             references t2(x) on delete cascade;
alter table t2 add constraint c2 foreign key (y) 
                              references t1(b) on delete cascade;

create table t1temp(l int , m int, op char(2));

create trigger trig_cyclic_del after DELETE on t1
REFERENCING OLD_Table AS deletedrows
for each statement mode db2sql 
insert into t1temp  select a, b,  'ad' from deletedrows;

select * from t1; 
select * from t2;
---following delete should delete all the rows
delete from t1 where a = 3;
select * from t1; 
select * from t2;
select * from t1temp;
delete from t1;

select * from t1; 
select * from t2;
select * from t1temp;


drop table t1temp;
alter table t1 drop constraint c1;
drop table t2;
drop table t1;

-- triggers on a cyclic referential actions
create table t1(a int not null primary key, b int not null unique);
create table t2(x int not null primary key, y int);

insert into t1 values (1, 2);
insert into t1 values (2, 1);
insert into t2 values (1, 2);
insert into t2 values (2, 1);

insert into t1 values (3, 4);
insert into t1 values (4, 3);
insert into t2 values (3, 4);
insert into t2 values (4, 3);

insert into t1 values (6, 7);
insert into t1 values (7, 6);
insert into t2 values (6, 7);
insert into t2 values (7, 6);


alter table t1 add constraint c1 foreign key (b) 
                             references t2(x) on delete cascade;
alter table t2 add constraint c2 foreign key (y) 
                              references t1(b) on delete cascade;

create table t1temp(l int , m int, op char(2));

create trigger trig_cyclic_del AFTER DELETE on t1
REFERENCING OLD_Table AS deletedrows
for each statement mode db2sql 
insert into t1temp  select a, b,  'bd' from deletedrows;

select * from t1; 
select * from t2;
---following delete should delete all the rows
delete from t1 where a = 3;
select * from t1; 
select * from t2;
select * from t1temp;

delete from t1;

select * from t1; 
select * from t2;
select * from t1temp;


drop table t1temp;
alter table t1 drop constraint c1;
drop table t2;
drop table t1;


--ROW triggers on a cyclic referential actions
create table t1(a int not null primary key, b int not null unique);
create table t2(x int not null primary key, y int);

insert into t1 values (1, 2);
insert into t1 values (2, 1);
insert into t2 values (1, 2);
insert into t2 values (2, 1);

insert into t1 values (3, 4);
insert into t1 values (4, 3);
insert into t2 values (3, 4);
insert into t2 values (4, 3);

insert into t1 values (6, 7);
insert into t1 values (7, 6);
insert into t2 values (6, 7);
insert into t2 values (7, 6);


alter table t1 add constraint c1 foreign key (b) 
                             references t2(x) on delete cascade;
alter table t2 add constraint c2 foreign key (y) 
                              references t1(b) on delete cascade;

create table t1temp(l int , m int, op char(2));

create trigger trig_cyclic_del1 after DELETE on t1
referencing old as deletedrow
for each row mode db2sql
insert into t1temp values(deletedrow.a , deletedrow.b, 'ad');

create trigger trig_cyclic_del2 AFTER DELETE on t1
referencing old as deletedrow
for each row mode db2sql
insert into t1temp values(deletedrow.a , deletedrow.b, 'bd');

select * from t1; 
select * from t2;
---following delete should delete all the rows
delete from t1 where a = 1;
select * from t1; 
select * from t2;
select * from t1temp;
delete from t1;

select * from t1; 
select * from t2;
select * from t1temp;

drop table t1temp;
alter table t1 drop constraint c1;
drop table t2;
drop table t1;

--SET NULL UPDATE  STETEMENT triggers on a self referencing table

create table emp(empno char(2) not null, mgr char(2), constraint emp primary key(empno),
  constraint manages foreign key(mgr) references emp(empno) on delete set null);

create table tempemp(empno char(2) , mgr char(2)  , op char(2));

insert into emp values('e1', null);
insert into emp values('e2', 'e1');
insert into emp values('e3', 'e1');
insert into emp values('e4', 'e2');
insert into emp values('e5', 'e4');
insert into emp values('e6', 'e5');
insert into emp values('e7', 'e6');
insert into emp values('e8', 'e7');
insert into emp values('e9', 'e8');


create trigger trig_emp_delete AFTER UPDATE on emp
REFERENCING OLD_Table AS updatedrows
for each statement mode db2sql 
insert into tempemp select empno, mgr,  'bu' from updatedrows;

create trigger trig_emp_delete1 AFTER UPDATE on emp
REFERENCING NEW_Table AS updatedrows
for each statement mode db2sql 
insert into tempemp select empno, mgr,  'au' from updatedrows;

delete from emp where empno = 'e1';

select * from emp;
select * from tempemp;

drop table emp;
drop table tempemp;

--SET NULL UPDATE  ROW triggers on a self referencing table

create table emp(empno char(2) not null, mgr char(2), constraint emp primary key(empno),
  constraint manages foreign key(mgr) references emp(empno) on delete set null);

create table tempemp(empno char(2) , mgr char(2)  , op char(2));

insert into emp values('e1', null);
insert into emp values('e2', 'e1');
insert into emp values('e3', 'e1');
insert into emp values('e4', 'e2');
insert into emp values('e5', 'e4');
insert into emp values('e6', 'e5');
insert into emp values('e7', 'e6');
insert into emp values('e8', 'e7');
insert into emp values('e9', 'e8');


create trigger trig_emp_delete after UPDATE on emp
REFERENCING OLD AS updatedrow
for each row mode db2sql
insert into tempemp values(updatedrow.empno, updatedrow.mgr, 'bu');

create trigger trig_emp_delete1 AFTER UPDATE on emp
REFERENCING NEW AS updatedrow
for each  row mode db2sql
insert into tempemp values(updatedrow.empno, updatedrow.mgr, 'au');

delete from emp where empno = 'e1';

select * from emp;
select * from tempemp;

delete from emp;
select * from emp;
select * from tempemp;

drop table emp;
drop table tempemp;

-- prepared statements check like in cview
create table t1(a int not null primary key);
create table t2(b int references t1(a) ON DELETE SET NULL);
insert into t1 values (1) , (2) , (3) , (4) ;
insert into t2 values (1) , (2) , (3) , (4) ;
autocommit off;
prepare sdelete as 'delete from t1 where a = ?' ;
execute sdelete using 'values (1)';
execute sdelete using 'values (2)';
commit;
select * from t2;
execute sdelete using 'values (3)';
execute sdelete using 'values (4)';
commit;
remove sdelete;
drop table t2;
create table t2(b int references t1(a) ON DELETE CASCADE);
insert into t1 values (1) , (2) , (3) , (4) ;
insert into t2 values (1) , (2) , (3) , (4) ;
prepare sdelete as 'delete from t1 where a = ?' ;
execute sdelete using 'values (1)';
execute sdelete using 'values (2)';
commit;
select * from t2;
execute sdelete using 'values (3)';
execute sdelete using 'values (4)';
commit;
remove sdelete;
drop table t2;
drop table t1;
autocommit on;
--make sure prepared statements are recompiled after a DDL changes works
create table t1(a int not null primary key);
create table t2(b int references t1(a) ON DELETE CASCADE, c int);
insert into t1 values (1) , (2) , (3) , (4) ;
insert into t2 values (1, 1) , (2, 2) , (3, 3) , (4, 4) ;
autocommit off;
prepare sdelete as 'delete from t1 where a = ?' ;
execute sdelete using 'values (1)';
execute sdelete using 'values (2)';
commit;
select * from t2;
create index idx1 on t2(c) ;
execute sdelete using 'values (3)';
execute sdelete using 'values (4)';
commit;
drop table t2;
commit;
insert into t1 values(5);
execute sdelete using 'values (5)';
select * from t1;
remove sdelete;
autocommit on;
drop table t1;
commit;

--do some rollbacks that involved prepared statement executtions
create table t1(a int not null primary key);
create table t2(b int references t1(a) ON DELETE CASCADE, c int);
insert into t1 values (1) , (2) , (3) , (4) ;
insert into t2 values (1, 1) , (2, 2) , (3, 3) , (4, 4) ;
commit;
autocommit off;
prepare sdelete as 'delete from t1 where a = ?' ;
execute sdelete using 'values (1)';
execute sdelete using 'values (2)';
rollback;
select * from t2;
execute sdelete using 'values (3)';
create index idx1 on t2(c) ;
execute sdelete using 'values (4)';
commit;
select * from t1;
select * from t2;
drop table t2;
rollback;
insert into t1 values(5);
execute sdelete using 'values (5)';
select * from t1;
select * from t2;
remove sdelete;
autocommit on;
drop table t2;
drop table t1;

---UNIQUE COLUMN NOT NULL VALUE CHECKS
--delete cascade on non-nullable unique column
create table t1 ( a int not null unique) ;
insert into t1 values(0) ;
insert into t1 values(1) ;
insert into t1 values(2) ;
create table t2(b int references t1(a) ON DELETE CASCADE) ;
insert into t2 values(null) ;
insert into t2 values(null) ;
insert into t2 values(null) ;
insert into t2 values(null) ;
insert into t2 values(null) ;
insert into t2 values(null) ;
insert into t2 values(null) ;
select * from t1 ;
select * from t2 ;

delete from t1 where a = 0 ;

select * from t1 ;
-- null values from t1 are not deleted
select * from t2 ;


drop table t2;
drop table t1;

--self ref foreign key without null values

create table t1( a int not null unique , b int references t1(a) 
ON DELETE SET NULL);

insert into t1 values ( 1 , null) ;
delete from t1 where b is null ;

select * from t1 ;

drop table t1 ;

create table t1( a int not null unique , b int references t1(a) 
ON DELETE CASCADE);
insert into t1 values ( 1 , null) ;
insert into t1 values ( 0 , 1) ;
delete from t1 where b is null ;
select * from t1 ;
drop table t1 ;

--mutiple tables
create table parent( a int not null unique) ;
create table child1(b int not null unique references parent(a)
ON DELETE CASCADE);
create table child2(c int not null unique references child1(b)
ON DELETE CASCADE);

insert into parent values(0) ;
insert into parent values(1) ;
insert into parent values(2) ;
insert into child1 values(0) ;
insert into child1 values(1) ;
insert into child1 values(2) ;
insert into child2 values(0) ;
insert into child2 values(1) ;
insert into child2 values(2) ;

select * from parent ;
select * from child1;
select * from child2 ;

delete from parent where a = 1 ;
select * from parent ;
select * from child1;
select * from child2 ;

delete from parent where a = 0 ;
select * from parent ;
select * from child1;

--delete all the rows
delete from parent;

drop table child2;
create table child2(c int references child1(b)
ON DELETE SET NULL);

insert into parent values(0) ;
insert into parent values(1) ;
insert into parent values(2) ;
insert into child1 values(0) ;
insert into child1 values(1) ;
insert into child1 values(2) ;
insert into child2 values(null) ;
insert into child2 values(1) ;
insert into child2 values(2) ;

select * from parent ;
select * from child1;
select * from child2 ;

delete from parent where a = 1 ;
select * from parent ;
select * from child1;
select * from child2;

delete from parent where a = 0;
select * from parent ;
select * from child1;
select * from child2;