📄 triggergeneral.out
字号:
ij> ---- General trigger test--create function printTriggerInfo() returns varchar(1) PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Triggers.printTriggerInfo';0 rows inserted/updated/deletedij> create function triggerFiresMin(s varchar(128)) returns varchar(1) PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Triggers.triggerFiresMinimal';0 rows inserted/updated/deletedij> create function triggerFires(s varchar(128)) returns varchar(1) PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Triggers.triggerFires';0 rows inserted/updated/deletedij> create function begInvRefToTECTest() returns varchar(1) PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Triggers.beginInvalidRefToTECTest';0 rows inserted/updated/deletedij> create procedure notifyDMLDone() PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Triggers.notifyDMLDone';0 rows inserted/updated/deletedij> drop table x;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'X' because it does not exist.ij> create table x (x int, y int, z int, constraint ck1 check (x > 0));0 rows inserted/updated/deletedij> create view v as select * from x;0 rows inserted/updated/deletedij> -- okcreate trigger t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;0 rows inserted/updated/deletedij> -- trigger already existscreate trigger t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;ERROR X0Y32: Trigger 'T1' already exists in Schema 'APP'.ij> -- trigger already existscreate trigger app.t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;ERROR X0Y32: Trigger 'T1' already exists in Schema 'APP'.ij> -- make sure system tables look as we expectselect cast(triggername as char(10)), event, firingtime, type, state, referencedcolumns from sys.systriggers;1 |&|&|&|&|REFERENCEDCOLU&----------------------------------T1 |U|B|R|E|(1,2) ij> select cast(triggername as char(10)), CAST (TRIGGERDEFINITION AS VARCHAR(180)), STMTNAME from sys.systriggers t, sys.sysstatements s where s.stmtid = t.actionstmtid;1 |2 |STMTNAME ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------T1 |values 1 |TRIGGERACTN_xxxxFILTERED-UUIDxxxx_xxxxFILTERED-UUIDxxxx ij> select cast(triggername as char(10)), tablename from sys.systriggers t, sys.systables tb where t.tableid = tb.tableid;1 |TABLENAME -------------------------------------------------------------------------------------------------------------------------------------------T1 |X ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', 'SYSTRIGGERS');1 -----------1 ij> drop trigger t1;0 rows inserted/updated/deletedij> -- not in sys schemacreate trigger sys.tr NO CASCADE before insert on x for each row mode db2sql values 1;ERROR 42X62: 'CREATE TRIGGER' is not allowed in the 'SYS' schema.ij> -- not on table in sys schemacreate trigger tr NO CASCADE before insert on sys.systables for each row mode db2sql values 1;ERROR X0Y56: 'CREATE TRIGGER' is not allowed on the System table 'SYS.SYSTABLES'.ij> -- duplicate columns, not allowedcreate trigger tr NO CASCADE before update of x, x on x for each row mode db2sql values 1;ERROR 42Y40: 'X' appears multiple times in the UPDATE OF column list for trigger 'TR'.ij> -- no params in column listcreate trigger tr NO CASCADE before update of x, ? on x for each row mode db2sql values 1;ERROR 42X01: Syntax error: Encountered "?" at line 2, column 50.ij> -- invalid columncreate trigger tr NO CASCADE before update of doesnotexist on x for each row mode db2sql values 1;ERROR 42X14: 'DOESNOTEXIST' is not a column in table or VTI 'X'.ij> -- not on viewcreate trigger tr NO CASCADE before insert on v for each row mode db2sql values 1;ERROR 42Y62: 'CREATE TRIGGER' is not allowed on 'APP.V' because it is a view.ij> -- error to use table qualifiercreate trigger tr NO CASCADE before update of x.x on x for each row mode db2sql values 1;ERROR 42X01: Syntax error: Encountered "." at line 2, column 48.ij> -- error to use schema.table qualifiercreate trigger tr NO CASCADE before update of app.x.x on x for each row mode db2sql values 1;ERROR 42X01: Syntax error: Encountered "." at line 2, column 50.ij> -- no params in trigger action-- badcreate trigger tr NO CASCADE before delete on x for each row mode db2sql select * from x where x = ?;ERROR 42Y27: Parameters are not allowed in the trigger action.ij> create trigger stmttrigger NO CASCADE before delete on x for each statement mode db2sql values 1;0 rows inserted/updated/deletedij> select triggername, type from sys.systriggers where triggername = 'STMTTRIGGER';TRIGGERNAME |&----------------------------------------------------------------------------------------------------------------------------------STMTTRIGGER |Sij> drop trigger stmttrigger;0 rows inserted/updated/deletedij> create trigger rowtrigger NO CASCADE before delete on x for each row mode db2sql values 1;0 rows inserted/updated/deletedij> select triggername, type from sys.systriggers where triggername = 'ROWTRIGGER';TRIGGERNAME |&----------------------------------------------------------------------------------------------------------------------------------ROWTRIGGER |Rij> drop trigger rowtrigger;0 rows inserted/updated/deletedij> -- fool around with depedencies-- CREATE TRIGGERcreate trigger t2 NO CASCADE before update of x,y on x for each row mode db2sql values 1;0 rows inserted/updated/deletedij> -- CREATE CONSTRAINTalter table x add constraint ck2 check(x > 0);0 rows inserted/updated/deletedij> -- DROP VIEWdrop view v;0 rows inserted/updated/deletedij> -- CREATE VIEWcreate view v as select * from x;0 rows inserted/updated/deletedij> -- CREATE INDEXcreate index ix on x(x);0 rows inserted/updated/deletedij> -- DROP TRIGGER: to the other types we have heredrop trigger t2;0 rows inserted/updated/deletedij> -- DROP INDEXdrop index ix;0 rows inserted/updated/deletedij> -- DROP CONSTRAINTalter table x drop constraint ck2;0 rows inserted/updated/deletedij> -- MAKE SURE TRIGGER SPS IS RECOMPILED IF TABLE IS ALTERED.create table y (x int, y int, z int);0 rows inserted/updated/deletedij> create trigger tins after insert on x referencing new_table as newtab for each statement mode db2sql insert into y select x, y, z from newtab;0 rows inserted/updated/deletedij> insert into x values (1, 1, 1);1 row inserted/updated/deletedij> alter table x add column w int default 100;0 rows inserted/updated/deletedij> alter table x add constraint nonulls check (w is not null);0 rows inserted/updated/deletedij> insert into x values (2, 2, 2, 2);1 row inserted/updated/deletedij> select * from y;X |Y |Z -----------------------------------1 |1 |1 2 |2 |2 ij> drop trigger tins;0 rows inserted/updated/deletedij> drop table y;0 rows inserted/updated/deletedij> -- prove that by dropping the underlying table, we have dropped the trigger-- first, lets create a few other triggerscreate trigger t2 NO CASCADE before update of x,y on x for each row mode db2sql values 1;0 rows inserted/updated/deletedij> create trigger t3 after update of x,y on x for each statement mode db2sql values 1;0 rows inserted/updated/deletedij> create trigger t4 after delete on x for each statement mode db2sql values 1;0 rows inserted/updated/deletedij> select cast(triggername as char(10)), tablename from sys.systriggers t, sys.systables tb where t.tableid = tb.tableid order by 1;1 |TABLENAME -------------------------------------------------------------------------------------------------------------------------------------------T2 |X T3 |X T4 |X ij> drop view v;0 rows inserted/updated/deletedij> drop table x;0 rows inserted/updated/deletedij> select cast(triggername as char(10)), tablename from sys.systriggers t, sys.systables tb where t.tableid = tb.tableid order by 1;1 |TABLENAME -------------------------------------------------------------------------------------------------------------------------------------------ij> ---- schema testing--create table x (x int, y int, z int);0 rows inserted/updated/deletedij> create schema test;0 rows inserted/updated/deletedij> create trigger test.t1 NO CASCADE before delete on x for each row mode db2sql values 1;0 rows inserted/updated/deletedij> set schema test;0 rows inserted/updated/deletedij> create trigger t2 NO CASCADE before delete on app.x for each row mode db2sql values 1;0 rows inserted/updated/deletedij> select schemaname, triggername from sys.systriggers t, sys.sysschemas s where s.schemaid = t.schemaid;SCHEMANAME |TRIGGERNAME -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TEST |T1 TEST |T2 ij> set schema app;0 rows inserted/updated/deletedij> -- failsdrop schema test restrict;ERROR X0Y54: Schema 'TEST' cannot be dropped because it is not empty.ij> drop trigger test.t2;0 rows inserted/updated/deletedij> -- failsdrop schema test restrict;ERROR X0Y54: Schema 'TEST' cannot be dropped because it is not empty.ij> set schema test;0 rows inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> set schema app;0 rows inserted/updated/deletedij> -- ok this timedrop schema test restrict;0 rows inserted/updated/deletedij> ---- Test the information in the trigger information context--create table t (x int, y int, c char(1));0 rows inserted/updated/deletedij> create trigger t1 NO CASCADE before insert on t for each statement mode db2sql values app.printTriggerInfo();0 rows inserted/updated/deletedij> insert into t values (1,1,'1');TriggerInformation------------------getEventStatetmentText(): insert into t values (1,1,'1')getEventType(): INSERTgetModifiedColumns(): { <all> }wasColumnModified() on each column X: true Y: true C: trueBEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {1,1,1}1 row inserted/updated/deletedij> delete from t;1 row inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> create trigger t1 after insert on t for each statement mode db2sql values app.printTriggerInfo();0 rows inserted/updated/deletedij> insert into t values (1,1,'1');TriggerInformation------------------getEventStatetmentText(): insert into t values (1,1,'1')getEventType(): INSERTgetModifiedColumns(): { <all> }wasColumnModified() on each column X: true Y: true C: trueBEFORE RESULT SET<NULL>AFTER RESULT SET X,Y,C - - - {1,1,1}1 row inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> create trigger t1 NO CASCADE before update on t for each statement mode db2sql values app.printTriggerInfo();0 rows inserted/updated/deletedij> update t set x = 2;TriggerInformation------------------getEventStatetmentText(): update t set x = 2getEventType(): UPDATEgetModifiedColumns(): {X}wasColumnModified() on each column X: true Y: false C: falseBEFORE RESULT SET X,Y,C - - - {1,1,1}AFTER RESULT SET X,Y,C - - - {2,1,1}1 row inserted/updated/deletedij> update t set y = 2, c = '2';TriggerInformation------------------getEventStatetmentText(): update t set y = 2, c = '2'getEventType(): UPDATEgetModifiedColumns(): {Y, C}wasColumnModified() on each column X: false Y: true C: trueBEFORE RESULT SET X,Y,C - - - {2,1,1}AFTER RESULT SET X,Y,C - - - {2,2,2}1 row inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> create trigger t1 after update on t for each statement mode db2sql values app.printTriggerInfo();0 rows inserted/updated/deletedij> update t set x = 3;TriggerInformation------------------getEventStatetmentText(): update t set x = 3getEventType(): UPDATEgetModifiedColumns(): {X}wasColumnModified() on each column X: true Y: false C: falseBEFORE RESULT SET X,Y,C - - - {2,2,2}AFTER RESULT SET X,Y,C - - - {3,2,2}1 row inserted/updated/deletedij> update t set y = 3, c = '3';TriggerInformation------------------getEventStatetmentText(): update t set y = 3, c = '3'getEventType(): UPDATEgetModifiedColumns(): {Y, C}wasColumnModified() on each column X: false Y: true C: trueBEFORE RESULT SET X,Y,C - - - {3,2,2}AFTER RESULT SET X,Y,C - - - {3,3,3}1 row inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> create trigger t1 no cascade before delete on t for each statement mode db2sql values app.printTriggerInfo();0 rows inserted/updated/deletedij> delete from t;TriggerInformation------------------getEventStatetmentText(): delete from tgetEventType(): DELETEgetModifiedColumns(): { <all> }wasColumnModified() on each column X: true Y: true C: trueBEFORE RESULT SET X,Y,C - - - {3,3,3}AFTER RESULT SET<NULL>1 row inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> insert into t values(3,3,'3');1 row inserted/updated/deletedij> create trigger t1 after delete on t for each statement mode db2sql values app.printTriggerInfo();0 rows inserted/updated/deletedij> delete from t;TriggerInformation------------------getEventStatetmentText(): delete from tgetEventType(): DELETEgetModifiedColumns(): { <all> }wasColumnModified() on each column X: true Y: true C: trueBEFORE RESULT SET X,Y,C - - - {3,3,3}AFTER RESULT SET<NULL>1 row inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> ---- Test trigger firing order--create trigger t1 after insert on t for each row mode db2sql values app.triggerFiresMin('3rd');0 rows inserted/updated/deletedij> create trigger t2 after insert on t for each statement mode db2sql values app.triggerFiresMin('1st');0 rows inserted/updated/deletedij> create trigger t3 no cascade before insert on t for each row mode db2sql values app.triggerFiresMin('4th');0 rows inserted/updated/deletedij> create trigger t4 after insert on t for each row mode db2sql values app.triggerFiresMin('2nd');0 rows inserted/updated/deletedij> create trigger t5 no cascade before insert on t for each statement mode db2sql values app.triggerFiresMin('5th');0 rows inserted/updated/deletedij> insert into t values (1,1,'1');TRIGGER: <4th>TRIGGER: <5th>TRIGGER: <3rd>TRIGGER: <1st>TRIGGER: <2nd>1 row inserted/updated/deletedij> drop trigger t1;0 rows inserted/updated/deletedij> drop trigger t2;0 rows inserted/updated/deletedij> drop trigger t3;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -