📄 refactions.out
字号:
4 |3 |bd 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 |bd 4 |3 |bd 1 |2 |bd 2 |1 |bd 6 |7 |bd 7 |6 |bd 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> --ROW 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_del1 after DELETE on t1referencing old as deletedrowfor each row mode db2sqlinsert into t1temp values(deletedrow.a , deletedrow.b, 'ad');0 rows inserted/updated/deletedij> create trigger trig_cyclic_del2 AFTER DELETE on t1referencing old as deletedrowfor each row mode db2sqlinsert into t1temp values(deletedrow.a , deletedrow.b, 'bd');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 = 1;1 row inserted/updated/deletedij> select * from t1;A |B -----------------------3 |4 4 |3 6 |7 7 |6 ij> select * from t2;X |Y -----------------------3 |4 4 |3 6 |7 7 |6 ij> select * from t1temp;L |M |OP ----------------------------1 |2 |ad 2 |1 |ad 1 |2 |bd 2 |1 |bd 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 ----------------------------1 |2 |ad 2 |1 |ad 1 |2 |bd 2 |1 |bd 3 |4 |ad 4 |3 |ad 6 |7 |ad 7 |6 |ad 3 |4 |bd 4 |3 |bd 6 |7 |bd 7 |6 |bd 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> --SET NULL UPDATE STETEMENT 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 set null);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 UPDATE on empREFERENCING OLD_Table AS updatedrowsfor each statement mode db2sql insert into tempemp select empno, mgr, 'bu' from updatedrows;0 rows inserted/updated/deletedij> create trigger trig_emp_delete1 AFTER UPDATE on empREFERENCING NEW_Table AS updatedrowsfor each statement mode db2sql insert into tempemp select empno, mgr, 'au' from updatedrows;0 rows inserted/updated/deletedij> delete from emp where empno = 'e1';1 row inserted/updated/deletedij> select * from emp;E&|MGR -------e2|NULLe3|NULLe4|e2 e5|e4 e6|e5 e7|e6 e8|e7 e9|e8 ij> select * from tempemp;EMP&|MGR |OP --------------e2 |e1 |bu e3 |e1 |bu e2 |NULL|au e3 |NULL|au ij> drop table emp;0 rows inserted/updated/deletedij> drop table tempemp;0 rows inserted/updated/deletedij> --SET NULL UPDATE ROW 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 set null);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 UPDATE on empREFERENCING OLD AS updatedrowfor each row mode db2sqlinsert into tempemp values(updatedrow.empno, updatedrow.mgr, 'bu');0 rows inserted/updated/deletedij> create trigger trig_emp_delete1 AFTER UPDATE on empREFERENCING NEW AS updatedrowfor each row mode db2sqlinsert into tempemp values(updatedrow.empno, updatedrow.mgr, 'au');0 rows inserted/updated/deletedij> delete from emp where empno = 'e1';1 row inserted/updated/deletedij> select * from emp;E&|MGR -------e2|NULLe3|NULLe4|e2 e5|e4 e6|e5 e7|e6 e8|e7 e9|e8 ij> select * from tempemp;EMP&|MGR |OP --------------e2 |e1 |bu e3 |e1 |bu e2 |NULL|au e3 |NULL|au ij> delete from emp;8 rows inserted/updated/deletedij> select * from emp;E&|MGR -------ij> select * from tempemp;EMP&|MGR |OP --------------e2 |e1 |bu e3 |e1 |bu e2 |NULL|au e3 |NULL|au e4 |e2 |bu e5 |e4 |bu e6 |e5 |bu e7 |e6 |bu e8 |e7 |bu e9 |e8 |bu e4 |NULL|au e5 |NULL|au e6 |NULL|au e7 |NULL|au e8 |NULL|au e9 |NULL|au ij> drop table emp;0 rows inserted/updated/deletedij> drop table tempemp;0 rows inserted/updated/deletedij> -- prepared statements check like in cviewcreate table t1(a int not null primary key);0 rows inserted/updated/deletedij> create table t2(b int references t1(a) ON DELETE SET NULL);0 rows inserted/updated/deletedij> insert into t1 values (1) , (2) , (3) , (4) ;4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4) ;4 rows inserted/updated/deletedij> autocommit off;ij> prepare sdelete as 'delete from t1 where a = ?' ;ij> execute sdelete using 'values (1)';1 row inserted/updated/deletedij> execute sdelete using 'values (2)';1 row inserted/updated/deletedij> commit;ij> select * from t2;B -----------3 4 NULL NULL ij> execute sdelete using 'values (3)';1 row inserted/updated/deletedij> execute sdelete using 'values (4)';1 row inserted/updated/deletedij> commit;ij> remove sdelete;ij> drop table t2;0 rows inserted/updated/deletedij> create table t2(b int references t1(a) ON DELETE CASCADE);0 rows inserted/updated/deletedij> insert into t1 values (1) , (2) , (3) , (4) ;4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4) ;4 rows inserted/updated/deletedij> prepare sdelete as 'delete from t1 where a = ?' ;ij> execute sdelete using 'values (1)';1 row inserted/updated/deletedij> execute sdelete using 'values (2)';1 row inserted/updated/deletedij> commit;ij> select * from t2;B -----------3 4 ij> execute sdelete using 'values (3)';1 row inserted/updated/deletedij> execute sdelete using 'values (4)';1 row inserted/updated/deletedij> commit;ij> remove sdelete;ij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> autocommit on;ij> --make sure prepared statements are recompiled after a DDL changes workscreate table t1(a int not null primary key);0 rows inserted/updated/deletedij> create table t2(b int references t1(a) ON DELETE CASCADE, c int);0 rows inserted/updated/deletedij> insert into t1 values (1) , (2) , (3) , (4) ;4 rows inserted/updated/deletedij> insert into t2 values (1, 1) , (2, 2) , (3, 3) , (4, 4) ;4 rows inserted/updated/deletedij> autocommit off;ij> prepare sdelete as 'delete from t1 where a = ?' ;ij> execute sdelete using 'values (1)';1 row inserted/updated/deletedij> execute sdelete using 'values (2)';1 row inserted/updated/deletedij> commit;ij> select * from t2;B |C -----------------------3 |3 4 |4 ij> create index idx1 on t2(c) ;0 rows inserted/updated/deletedij> execute sdelete using 'values (3)';1 row inserted/updated/deletedij> execute sdelete using 'values (4)';1 row inserted/updated/deletedij> commit;ij> drop table t2;0 rows inserted/updated/deletedij> commit;ij> insert into t1 values(5);1 row inserted/updated/deletedij> execute sdelete using 'values (5)';1 row inserted/updated/deletedij> select * from t1;A -----------ij> remove sdelete;ij> autocommit on;ij> drop table t1;0 rows inserted/updated/deletedij> commit;ij> --do some rollbacks that involved prepared statement executtionscreate table t1(a int not null primary key);0 rows inserted/updated/deletedij> create table t2(b int references t1(a) ON DELETE CASCADE, c int);0 rows inserted/updated/deletedij> insert into t1 values (1) , (2) , (3) , (4) ;4 rows inserted/updated/deletedij> insert into t2 values (1, 1) , (2, 2) , (3, 3) , (4, 4) ;4 rows inserted/updated/deletedij> commit;ij> autocommit off;ij> prepare sdelete as 'delete from t1 where a = ?' ;ij> execute sdelete using 'values (1)';1 row inserted/updated/deletedij> execute sdelete using 'values (2)';1 row inserted/updated/deletedij> rollback;ij> select * from t2;B |C -----------------------1 |1 2 |2 3 |3 4 |4 ij> execute sdelete using 'values (3)';1 row inserted/updated/deletedij> create index idx1 on t2(c) ;0 rows inserted/updated/deletedij> execute sdelete using 'values (4)';1 row inserted/updated/deletedij> commit;ij> select * from t1;A -----------1 2 ij> select * from t2;B |C -----------------------1 |1 2 |2 ij> drop table t2;0 rows inserted/updated/deletedij> rollback;ij> insert into t1 values(5);1 row inserted/updated/deletedij> execute sdelete using 'values (5)';1 row inserted/updated/deletedij> select * from t1;A -----------1 2 ij> select * from t2;B |C -----------------------1 |1 2 |2 ij> remove sdelete;ij> autocommit on;ij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> ---UNIQUE COLUMN NOT NULL VALUE CHECKS--delete cascade on non-nullable unique columncreate table t1 ( a int not null unique) ;0 rows inserted/updated/deletedij> insert into t1 values(0) ;1 row inserted/updated/deletedij> insert into t1 values(1) ;1 row inserted/updated/deletedij> insert into t1 values(2) ;1 row inserted/updated/deletedij> create table t2(b int references t1(a) ON DELETE CASCADE) ;0 rows inserted/updated/deletedij> insert into t2 values(null) ;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -