📄 refactions.out
字号:
ij> --do the same case as above with RESTRICT--we should get error, because RESTRICT rules are checked before firing triggerscreate table t2(x int references t1(a) ON DELETE RESTRICT , y int);0 rows inserted/updated/deletedij> insert into t2 values(1, 2);1 row inserted/updated/deletedij> insert into t2 values(2, 3);1 row inserted/updated/deletedij> --following delete should throw constraint violations errordelete from t1 where a =1;ERROR 23503: DELETE on table 'T1' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (1). The statement has been rolled back.ij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --test for ON UPDATE RESTRICT--first check with a trigger and NO ACTIONautocommit off ;ij> create table t1(a int not null unique, b int not null unique);0 rows inserted/updated/deletedij> create table t2(x int references t1(a) ON UPDATE NO ACTION , y int);0 rows inserted/updated/deletedij> create trigger trig_update after UPDATE on t1referencing old as old for each row mode db2sqlupdate t2 set x = 2 where x = old.a;0 rows inserted/updated/deletedij> insert into t1 values(1 , 2);1 row inserted/updated/deletedij> insert into t1 values(2 , 3);1 row inserted/updated/deletedij> insert into t2 values(1, 2);1 row inserted/updated/deletedij> insert into t2 values(2, 3);1 row inserted/updated/deletedij> commit;ij> -- this update should fail-- parent row can not be deleted because of a dependent relationship from another tableupdate t1 set a = 7 where a =1;ERROR 23503: UPDATE on table 'T1' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (1). The statement has been rolled back.ij> -- should pass because no foreign key constraints are violatedupdate t1 set b = 7 where a =1;1 row inserted/updated/deletedij> select * from t1 ;A |B -----------------------1 |7 2 |3 ij> select * from t2 ;X |Y -----------------------2 |2 2 |3 ij> rollback;ij> drop table t2;0 rows inserted/updated/deletedij> commit;ij> --do the same case as above with RESTRICT--we should get error, because RESTRICT is check before firing triggerscreate table t2(x int references t1(a) ON UPDATE RESTRICT , y int);0 rows inserted/updated/deletedij> insert into t2 values(1, 2);1 row inserted/updated/deletedij> insert into t2 values(2, 3);1 row inserted/updated/deletedij> commit;ij> --following update should throw an errorupdate t1 set a = 7 where a =1;ERROR 23503: UPDATE on table 'T1' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (1). The statement has been rolled back.ij> select * from t1 ;A |B -----------------------1 |2 2 |3 ij> select * from t2;X |Y -----------------------1 |2 2 |3 ij> autocommit on;ij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --After ROW triggers on the dependen tablescreate table t1( a int not null primary key , b int , c int not null unique) ;0 rows inserted/updated/deletedij> create table t2( x int references t1(c) ON DELETE CASCADE ) ;0 rows inserted/updated/deletedij> create table t3( y int references t1(a) ON DELETE CASCADE) ;0 rows inserted/updated/deletedij> create table t4(z int , op char(2));0 rows inserted/updated/deletedij> --create triggers such a way that the all deleted row--in t2 are inserted into t4create trigger trig_delete after DELETE on t2referencing old as deletedrowfor each row mode db2sqlinsert into t4 values(deletedrow.x , 'ad');0 rows inserted/updated/deletedij> insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;3 rows inserted/updated/deletedij> insert into t2 values (3) , (6), (9) ;3 rows inserted/updated/deletedij> insert into t3 values (1) , (4) , (7) ;3 rows inserted/updated/deletedij> delete from t1 ;3 rows inserted/updated/deletedij> select * from t4;Z |OP ----------------3 |ad 6 |ad 9 |ad ij> select * from t1;A |B |C -----------------------------------ij> select * from t2;X -----------ij> select * from t3;Y -----------ij> drop table t4;0 rows inserted/updated/deletedij> drop table t3;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --After Statement triggers on the dependen tablescreate table t1( a int not null primary key , b int , c int not null unique) ;0 rows inserted/updated/deletedij> create table t2( x int references t1(c) ON DELETE CASCADE ) ;0 rows inserted/updated/deletedij> create table t3( y int references t1(a) ON DELETE CASCADE) ;0 rows inserted/updated/deletedij> create table t4(z int , op char(2));0 rows inserted/updated/deletedij> --create triggers such a way that the all deleted row--in t2 are inserted into t4create trigger trig_delete after DELETE on t2REFERENCING OLD_Table AS deletedrowsfor each statement mode db2sql insert into t4 select x, 'ad' from deletedrows;0 rows inserted/updated/deletedij> insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;3 rows inserted/updated/deletedij> insert into t2 values (3) , (6), (9) ;3 rows inserted/updated/deletedij> insert into t3 values (1) , (4) , (7) ;3 rows inserted/updated/deletedij> delete from t1 ;3 rows inserted/updated/deletedij> select * from t4;Z |OP ----------------3 |ad 6 |ad 9 |ad ij> select * from t1;A |B |C -----------------------------------ij> select * from t2;X -----------ij> select * from t3;Y -----------ij> drop table t4;0 rows inserted/updated/deletedij> drop table t3;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --After triggers on a self referencing tablecreate 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);0 rows inserted/updated/deletedij> create table tempemp(empno char(2) , mgr char(2) , op char(2));0 rows inserted/updated/deletedij> insert into emp values('e1', null);1 row inserted/updated/deletedij> insert into emp values('e2', 'e1');1 row inserted/updated/deletedij> insert into emp values('e3', 'e1');1 row inserted/updated/deletedij> insert into emp values('e4', 'e2');1 row inserted/updated/deletedij> insert into emp values('e5', 'e4');1 row inserted/updated/deletedij> insert into emp values('e6', 'e5');1 row inserted/updated/deletedij> insert into emp values('e7', 'e6');1 row inserted/updated/deletedij> insert into emp values('e8', 'e7');1 row inserted/updated/deletedij> insert into emp values('e9', 'e8');1 row inserted/updated/deletedij> create trigger trig_emp_delete after DELETE on empREFERENCING OLD_Table AS deletedrowsfor each statement mode db2sql insert into tempemp select empno, mgr, 'ad' from deletedrows;0 rows inserted/updated/deletedij> delete from emp where empno = 'e1';1 row inserted/updated/deletedij> select * from emp;E&|MGR -------ij> select * from tempemp;EMP&|MGR |OP --------------e1 |NULL|ad e2 |e1 |ad e3 |e1 |ad e4 |e2 |ad e5 |e4 |ad e6 |e5 |ad e7 |e6 |ad e8 |e7 |ad e9 |e8 |ad ij> drop table emp;0 rows inserted/updated/deletedij> drop table tempemp;0 rows inserted/updated/deletedij> -- triggers on a self referencing tablecreate 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);0 rows inserted/updated/deletedij> create table tempemp(empno char(2) , mgr char(2) , op char(2));0 rows inserted/updated/deletedij> insert into emp values('e1', null);1 row inserted/updated/deletedij> insert into emp values('e2', 'e1');1 row inserted/updated/deletedij> insert into emp values('e3', 'e1');1 row inserted/updated/deletedij> insert into emp values('e4', 'e2');1 row inserted/updated/deletedij> insert into emp values('e5', 'e4');1 row inserted/updated/deletedij> insert into emp values('e6', 'e5');1 row inserted/updated/deletedij> insert into emp values('e7', 'e6');1 row inserted/updated/deletedij> insert into emp values('e8', 'e7');1 row inserted/updated/deletedij> insert into emp values('e9', 'e8');1 row inserted/updated/deletedij> create trigger trig_emp_delete AFTER DELETE on empREFERENCING OLD_Table AS deletedrowsfor each statement mode db2sql insert into tempemp select empno, mgr, 'bd' from deletedrows;0 rows inserted/updated/deletedij> delete from emp where empno = 'e1';1 row inserted/updated/deletedij> select * from emp;E&|MGR -------ij> select * from tempemp;EMP&|MGR |OP --------------e1 |NULL|bd e2 |e1 |bd e3 |e1 |bd e4 |e2 |bd e5 |e4 |bd e6 |e5 |bd e7 |e6 |bd e8 |e7 |bd e9 |e8 |bd ij> drop table emp;0 rows inserted/updated/deletedij> drop table tempemp;0 rows inserted/updated/deletedij> --After triggers on a cyclic referential actionscreate table t1(a int not null primary key, b int not null unique);0 rows inserted/updated/deletedij> create table t2(x int not null primary key, y int);0 rows inserted/updated/deletedij> insert into t1 values (1, 2);1 row inserted/updated/deletedij> insert into t1 values (2, 1);1 row inserted/updated/deletedij> insert into t2 values (1, 2);1 row inserted/updated/deletedij> insert into t2 values (2, 1);1 row inserted/updated/deletedij> insert into t1 values (3, 4);1 row inserted/updated/deletedij> insert into t1 values (4, 3);1 row inserted/updated/deletedij> insert into t2 values (3, 4);1 row inserted/updated/deletedij> insert into t2 values (4, 3);1 row inserted/updated/deletedij> insert into t1 values (6, 7);1 row inserted/updated/deletedij> insert into t1 values (7, 6);1 row inserted/updated/deletedij> insert into t2 values (6, 7);1 row inserted/updated/deletedij> insert into t2 values (7, 6);1 row inserted/updated/deletedij> alter table t1 add constraint c1 foreign key (b) references t2(x) on delete cascade;0 rows inserted/updated/deletedij> alter table t2 add constraint c2 foreign key (y) references t1(b) on delete cascade;0 rows inserted/updated/deletedij> create table t1temp(l int , m int, op char(2));0 rows inserted/updated/deletedij> create trigger trig_cyclic_del after DELETE on t1REFERENCING OLD_Table AS deletedrowsfor each statement mode db2sql insert into t1temp select a, b, 'ad' from deletedrows;0 rows inserted/updated/deletedij> select * from t1;A |B -----------------------1 |2 2 |1 3 |4 4 |3 6 |7 7 |6 ij> select * from t2;X |Y -----------------------1 |2 2 |1 3 |4 4 |3 6 |7 7 |6 ij> ---following delete should delete all the rowsdelete from t1 where a = 3;1 row inserted/updated/deletedij> select * from t1;A |B -----------------------1 |2 2 |1 6 |7 7 |6 ij> select * from t2;X |Y -----------------------1 |2 2 |1 6 |7 7 |6 ij> select * from t1temp;L |M |OP ----------------------------3 |4 |ad 4 |3 |ad ij> delete from t1;4 rows inserted/updated/deletedij> select * from t1;A |B -----------------------ij> select * from t2;X |Y -----------------------ij> select * from t1temp;L |M |OP ----------------------------3 |4 |ad 4 |3 |ad 1 |2 |ad 2 |1 |ad 6 |7 |ad 7 |6 |ad ij> drop table t1temp;0 rows inserted/updated/deletedij> alter table t1 drop constraint c1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> -- triggers on a cyclic referential actionscreate table t1(a int not null primary key, b int not null unique);0 rows inserted/updated/deletedij> create table t2(x int not null primary key, y int);0 rows inserted/updated/deletedij> insert into t1 values (1, 2);1 row inserted/updated/deletedij> insert into t1 values (2, 1);1 row inserted/updated/deletedij> insert into t2 values (1, 2);1 row inserted/updated/deletedij> insert into t2 values (2, 1);1 row inserted/updated/deletedij> insert into t1 values (3, 4);1 row inserted/updated/deletedij> insert into t1 values (4, 3);1 row inserted/updated/deletedij> insert into t2 values (3, 4);1 row inserted/updated/deletedij> insert into t2 values (4, 3);1 row inserted/updated/deletedij> insert into t1 values (6, 7);1 row inserted/updated/deletedij> insert into t1 values (7, 6);1 row inserted/updated/deletedij> insert into t2 values (6, 7);1 row inserted/updated/deletedij> insert into t2 values (7, 6);1 row inserted/updated/deletedij> alter table t1 add constraint c1 foreign key (b) references t2(x) on delete cascade;0 rows inserted/updated/deletedij> alter table t2 add constraint c2 foreign key (y) references t1(b) on delete cascade;0 rows inserted/updated/deletedij> create table t1temp(l int , m int, op char(2));0 rows inserted/updated/deletedij> create trigger trig_cyclic_del AFTER DELETE on t1REFERENCING OLD_Table AS deletedrowsfor each statement mode db2sql insert into t1temp select a, b, 'bd' from deletedrows;0 rows inserted/updated/deletedij> select * from t1;A |B -----------------------1 |2 2 |1 3 |4 4 |3 6 |7 7 |6 ij> select * from t2;X |Y -----------------------1 |2 2 |1 3 |4 4 |3 6 |7 7 |6 ij> ---following delete should delete all the rowsdelete from t1 where a = 3;1 row inserted/updated/deletedij> select * from t1;A |B -----------------------1 |2 2 |1 6 |7 7 |6 ij> select * from t2;X |Y -----------------------1 |2 2 |1 6 |7 7 |6 ij> select * from t1temp;L |M |OP ----------------------------3 |4 |bd ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -