📄 triggergeneral.out
字号:
0 rows inserted/updated/deletedij> drop trigger t4;0 rows inserted/updated/deletedij> drop trigger t5;0 rows inserted/updated/deletedij> -- try multiple values, make sure result sets don't get screwed up-- this time we'll print out result setscreate trigger t1 after insert on t for each row mode db2sql values app.triggerFires('3rd');0 rows inserted/updated/deletedij> create trigger t2 no cascade before insert on t for each statement mode db2sql values app.triggerFires('1st');0 rows inserted/updated/deletedij> create trigger t3 after insert on t for each row mode db2sql values app.triggerFires('4th');0 rows inserted/updated/deletedij> create trigger t4 no cascade before insert on t for each row mode db2sql values app.triggerFires('2nd');0 rows inserted/updated/deletedij> create trigger t5 after insert on t for each statement mode db2sql values app.triggerFires('5th');0 rows inserted/updated/deletedij> insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4');TRIGGER: <1st> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {2,2,2} {3,3,3} {4,4,4}TRIGGER: <2nd> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {2,2,2}TRIGGER: <2nd> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {3,3,3}TRIGGER: <2nd> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {4,4,4}TRIGGER: <3rd> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {2,2,2}TRIGGER: <3rd> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {3,3,3}TRIGGER: <3rd> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {4,4,4}TRIGGER: <4th> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {2,2,2}TRIGGER: <4th> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {3,3,3}TRIGGER: <4th> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {4,4,4}TRIGGER: <5th> on statement insert into t values (2,2,'2'), (3,3,'3'), (4,4,'4')BEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {2,2,2} {3,3,3} {4,4,4}3 rows inserted/updated/deletedij> delete from t;4 rows inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> drop trigger t2;0 rows inserted/updated/deletedij> drop trigger t3;0 rows inserted/updated/deletedij> drop trigger t4;0 rows inserted/updated/deletedij> drop trigger t5;0 rows inserted/updated/deletedij> ---- Test firing on empty change sets, -- statement triggers fire, row triggers-- do not.--create trigger t1 after insert on t for each row mode db2sql values app.triggerFires('ROW: empty insert, should NOT fire');0 rows inserted/updated/deletedij> create trigger t2 after insert on t for each statement mode db2sql values app.triggerFires('STATEMENT: empty insert, ok');0 rows inserted/updated/deletedij> insert into t select * from t;TRIGGER: <STATEMENT: empty insert, ok> on statement insert into t select * from tBEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - -0 rows inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> drop trigger t2;0 rows inserted/updated/deletedij> create trigger t1 after update on t for each row mode db2sql values app.triggerFires('ROW: empty update, should NOT fire');0 rows inserted/updated/deletedij> create trigger t2 after update on t for each statement mode db2sql values app.triggerFires('STATEMENT: empty update, ok');0 rows inserted/updated/deletedij> update t set x = x;TRIGGER: <STATEMENT: empty update, ok> on statement update t set x = xBEFORE RESULT SET X,Y,C - - -AFTER RESULT SET X,Y,C - - -0 rows inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> drop trigger t2;0 rows inserted/updated/deletedij> create trigger t1 after delete on t for each row mode db2sql values app.triggerFires('ROW: empty delete, should NOT fire');0 rows inserted/updated/deletedij> create trigger t2 after delete on t for each statement mode db2sql values app.triggerFires('STATEMENT: empty delete, ok');0 rows inserted/updated/deletedij> delete from t;TRIGGER: <STATEMENT: empty delete, ok> on statement delete from tBEFORE RESULT SET X,Y,C - - -AFTER RESULT SET<NULL>0 rows inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> drop trigger t2;0 rows inserted/updated/deletedij> drop table x;0 rows inserted/updated/deletedij> ---- After alter table, should pick up the new columns--create table talt(c1 int);0 rows inserted/updated/deletedij> create trigger tins after insert on talt for each statement mode db2sql values app.printTriggerInfo();0 rows inserted/updated/deletedij> create trigger tdel no cascade before delete on talt for each row mode db2sql values app.printTriggerInfo();0 rows inserted/updated/deletedij> create trigger tupd after update on talt for each statement mode db2sql values app.printTriggerInfo();0 rows inserted/updated/deletedij> insert into talt values (1);TriggerInformation------------------getEventStatetmentText(): insert into talt values (1)getEventType(): INSERTgetModifiedColumns(): { <all> }wasColumnModified() on each column C1: trueBEFORE RESULT SET<NULL>AFTER RESULT SET C1 -- {1}1 row inserted/updated/deletedij> alter table talt add column cnew int default null;0 rows inserted/updated/deletedij> select * from talt;C1 |CNEW -----------------------1 |NULL ij> insert into talt values (2,2);TriggerInformation------------------getEventStatetmentText(): insert into talt values (2,2)getEventType(): INSERTgetModifiedColumns(): { <all> }wasColumnModified() on each column C1: true CNEW: trueBEFORE RESULT SET<NULL>AFTER RESULT SET C1,CNEW -- ---- {2,2}1 row inserted/updated/deletedij> delete from talt;TriggerInformation------------------getEventStatetmentText(): delete from taltgetEventType(): DELETEgetModifiedColumns(): { <all> }wasColumnModified() on each column C1: true CNEW: trueBEFORE RESULT SET C1,CNEW -- ---- {1,null}AFTER RESULT SET<NULL>TriggerInformation------------------getEventStatetmentText(): delete from taltgetEventType(): DELETEgetModifiedColumns(): { <all> }wasColumnModified() on each column C1: true CNEW: trueBEFORE RESULT SET C1,CNEW -- ---- {2,2}AFTER RESULT SET<NULL>2 rows inserted/updated/deletedij> insert into talt values (3,3);TriggerInformation------------------getEventStatetmentText(): insert into talt values (3,3)getEventType(): INSERTgetModifiedColumns(): { <all> }wasColumnModified() on each column C1: true CNEW: trueBEFORE RESULT SET<NULL>AFTER RESULT SET C1,CNEW -- ---- {3,3}1 row inserted/updated/deletedij> update talt set cnew = 666;TriggerInformation------------------getEventStatetmentText(): update talt set cnew = 666getEventType(): UPDATEgetModifiedColumns(): {CNEW}wasColumnModified() on each column C1: false CNEW: trueBEFORE RESULT SET C1,CNEW -- ---- {3,3}AFTER RESULT SET C1,CNEW -- ---- {3,666}1 row inserted/updated/deletedij> drop trigger tins;0 rows inserted/updated/deletedij> drop trigger tdel;0 rows inserted/updated/deletedij> drop trigger tupd;0 rows inserted/updated/deletedij> -- make sure update w/ columns doesn't pick up new colcreate trigger tupd after update of c1 on talt for each statement mode db2sql values app.printTriggerInfo();0 rows inserted/updated/deletedij> alter table talt add column cnew2 int default null;0 rows inserted/updated/deletedij> insert into talt values (1,1,1);1 row inserted/updated/deletedij> update talt set cnew2 = 666;2 rows inserted/updated/deletedij> -- clean updrop table talt;0 rows inserted/updated/deletedij> ---- Trigger ordering wrt constraints--create table p (x int not null, constraint pk primary key (x));0 rows inserted/updated/deletedij> insert into p values 1,2,3;3 rows inserted/updated/deletedij> create table f (x int, constraint ck check (x > 0), constraint fk foreign key (x) references p);0 rows inserted/updated/deletedij> create trigger t1 no cascade before insert on f for each row mode db2sql values app.triggerFiresMin('BEFORE constraints');0 rows inserted/updated/deletedij> create trigger t2 after insert on f for each row mode db2sql values app.triggerFiresMin('AFTER constraints');0 rows inserted/updated/deletedij> -- INSERT-- fails, ck violatedinsert into f values 0;TRIGGER: <BEFORE constraints>ERROR 23513: The check constraint 'CK' was violated while performing an INSERT or UPDATE on table 'APP.F'.ij> alter table f drop constraint ck;0 rows inserted/updated/deletedij> -- fails, fk violatedinsert into f values 0;TRIGGER: <BEFORE constraints>ERROR 23503: INSERT on table 'F' caused a violation of foreign key constraint 'FK' for key (0). The statement has been rolled back.ij> alter table f drop foreign key fk;0 rows inserted/updated/deletedij> -- okinsert into f values 0;TRIGGER: <BEFORE constraints>TRIGGER: <AFTER constraints>1 row inserted/updated/deletedij> delete from f;1 row inserted/updated/deletedij> alter table f add constraint ck check (x > 0);0 rows inserted/updated/deletedij> alter table f add constraint fk foreign key (x) references p;0 rows inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> drop trigger t2;0 rows inserted/updated/deletedij> insert into f values (1);1 row inserted/updated/deletedij> -- UPDATEcreate trigger t1 no cascade before update on f for each row mode db2sql values app.triggerFiresMin('BEFORE constraints');0 rows inserted/updated/deletedij> create trigger t2 after update on f for each row mode db2sql values app.triggerFiresMin('AFTER constraints');0 rows inserted/updated/deletedij> -- fails, ck violatedupdate f set x = 0;TRIGGER: <BEFORE constraints>ERROR 23513: The check constraint 'CK' was violated while performing an INSERT or UPDATE on table 'APP.F'.ij> alter table f drop constraint ck;0 rows inserted/updated/deletedij> -- fails, fk violatedupdate f set x = 0;TRIGGER: <BEFORE constraints>ERROR 23503: UPDATE on table 'F' caused a violation of foreign key constraint 'FK' for key (0). The statement has been rolled back.ij> alter table f drop foreign key fk;0 rows inserted/updated/deletedij> -- okupdate f set x = 0;TRIGGER: <BEFORE constraints>TRIGGER: <AFTER constraints>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -