📄 triggergeneral.out
字号:
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> -- DELETEinsert into f values 1;1 row inserted/updated/deletedij> create trigger t1 no cascade before delete on p for each row mode db2sql values app.triggerFiresMin('BEFORE constraints');0 rows inserted/updated/deletedij> create trigger t2 after delete on p for each row mode db2sql values app.triggerFiresMin('AFTER constraints');0 rows inserted/updated/deletedij> -- fails, fk violateddelete from p;TRIGGER: <BEFORE constraints>TRIGGER: <BEFORE constraints>TRIGGER: <BEFORE constraints>ERROR 23503: DELETE on table 'P' caused a violation of foreign key constraint 'FK' for key (1). The statement has been rolled back.ij> alter table f drop foreign key fk;0 rows inserted/updated/deletedij> -- okdelete from p;TRIGGER: <BEFORE constraints>TRIGGER: <BEFORE constraints>TRIGGER: <BEFORE constraints>TRIGGER: <AFTER constraints>TRIGGER: <AFTER constraints>TRIGGER: <AFTER constraints>3 rows inserted/updated/deletedij> drop table f;0 rows inserted/updated/deletedij> drop table p;0 rows inserted/updated/deletedij> ---- Prove that we are firing the proper triggers based-- on the columns we are changing;--drop table t;0 rows inserted/updated/deletedij> create table t (c1 int, c2 int);0 rows inserted/updated/deletedij> create trigger tins after insert on t for each row mode db2sql values app.triggerFiresMin('insert');0 rows inserted/updated/deletedij> create trigger tdel after delete on t for each row mode db2sql values app.triggerFiresMin('delete');0 rows inserted/updated/deletedij> create trigger tupc1 after update of c1 on t for each row mode db2sql values app.triggerFiresMin('update c1');0 rows inserted/updated/deletedij> create trigger tupc2 after update of c2 on t for each row mode db2sql values app.triggerFiresMin('update c2');0 rows inserted/updated/deletedij> create trigger tupc1c2 after update of c1,c2 on t for each row mode db2sql values app.triggerFiresMin('update c1,c2');0 rows inserted/updated/deletedij> create trigger tupc2c1 after update of c2,c1 on t for each row mode db2sql values app.triggerFiresMin('update c2,c1');0 rows inserted/updated/deletedij> insert into t values (1,1);TRIGGER: <insert>1 row inserted/updated/deletedij> update t set c1 = 1;TRIGGER: <update c1>TRIGGER: <update c1,c2>TRIGGER: <update c2,c1>1 row inserted/updated/deletedij> update t set c2 = 1;TRIGGER: <update c2>TRIGGER: <update c1,c2>TRIGGER: <update c2,c1>1 row inserted/updated/deletedij> update t set c2 = 1, c1 = 1;TRIGGER: <update c1>TRIGGER: <update c2>TRIGGER: <update c1,c2>TRIGGER: <update c2,c1>1 row inserted/updated/deletedij> update t set c1 = 1, c2 = 1;TRIGGER: <update c1>TRIGGER: <update c2>TRIGGER: <update c1,c2>TRIGGER: <update c2,c1>1 row inserted/updated/deletedij> delete from t;TRIGGER: <delete>1 row inserted/updated/deletedij> -- Make sure that triggers work with delimited identifiers-- Make sure that text munging works correctlycreate table trigtable("cOlUmN1" int, "cOlUmN2 " int, "cOlUmN3"""" " int);0 rows inserted/updated/deletedij> create table trighistory("cOlUmN1" int, "cOlUmN2 " int, "cOlUmN3"""" " int);0 rows inserted/updated/deletedij> insert into trigtable values (1, 2, 3);1 row inserted/updated/deletedij> create trigger "tt1" after insert on trigtablereferencing NEW as NEW for each row mode db2sqlinsert into trighistory ("cOlUmN1", "cOlUmN2 ", "cOlUmN3"""" ") values (new."cOlUmN1" + 5, "NEW"."cOlUmN2 " * new."cOlUmN3"""" ", 5);0 rows inserted/updated/deletedij> maximumdisplaywidth 2000;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 and triggername = 'tt1';1 |2 |STMTNAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------tt1 |insert into trighistory ("cOlUmN1", "cOlUmN2 ", "cOlUmN3"""" ") values (new."cOlUmN1" + 5, "NEW"."cOlUmN2 " * new."cOlUmN3"""" ", 5) |TRIGGERACTN_xxxxFILTERED-UUIDxxxx_xxxxFILTERED-UUIDxxxx ij> insert into trigtable values (1, 2, 3);1 row inserted/updated/deletedij> select * from trighistory;cOlUmN1 |cOlUmN2 |cOlUmN3"" -----------------------------------6 |6 |5 ij> drop trigger "tt1";0 rows inserted/updated/deletedij> create trigger "tt1" after insert on trigtablereferencing new as new for each row mode db2sqlinsert into trighistory ("cOlUmN1", "cOlUmN2 ", "cOlUmN3"""" ") values (new."cOlUmN1" + new."cOlUmN1", "NEW"."cOlUmN2 " * new."cOlUmN3"""" ", new."cOlUmN2 " * 3);0 rows inserted/updated/deletedij> select cast(triggername as char(10)), CAST (TRIGGERDEFINITION AS VARCHAR(180)), STMTNAME from sys.systriggers t, sys.sysstatements s where s.stmtid = t.actionstmtid and triggername = 'tt1';1 |2 |STMTNAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------tt1 |insert into trighistory ("cOlUmN1", "cOlUmN2 ", "cOlUmN3"""" ") values (new."cOlUmN1" + new."cOlUmN1", "NEW"."cOlUmN2 " * new."cOlUmN3"""" ", new."cOlUmN2 " * 3) |TRIGGERACTN_xxxxFILTERED-UUIDxxxx_xxxxFILTERED-UUIDxxxx ij> insert into trigtable values (1, 2, 3);1 row inserted/updated/deletedij> select * from trighistory;cOlUmN1 |cOlUmN2 |cOlUmN3"" -----------------------------------6 |6 |5 2 |6 |6 ij> drop table trigtable;0 rows inserted/updated/deletedij> drop table trighistory;0 rows inserted/updated/deletedij> -- trigger bug that got fixed mysteriously-- between xena and buffycreate table trigtable1(c1 int, c2 int);0 rows inserted/updated/deletedij> create table trighistory(trigtable char(30), c1 int, c2 int);0 rows inserted/updated/deletedij> create trigger trigtable1 after update on trigtable1referencing OLD as oldtablefor each row mode db2sqlinsert into trighistory values ('trigtable1', oldtable.c1, oldtable.c2);0 rows inserted/updated/deletedij> insert into trigtable1 values (1, 1);1 row inserted/updated/deletedij> update trigtable1 set c1 = 11, c2 = 11;1 row inserted/updated/deletedij> select * from trighistory;TRIGTABLE |C1 |C2 ------------------------------------------------------trigtable1 |1 |1 ij> drop table trigtable1;0 rows inserted/updated/deletedij> drop table trighistory;0 rows inserted/updated/deletedij> -- -- Lets make sure that the tec cannot be accessed once-- the dml that caused it to be pushed is finished.--drop table t;0 rows inserted/updated/deletedij> create table t (x int);0 rows inserted/updated/deletedij> create trigger t no cascade before insert on t for each statement mode db2sql values app.begInvRefToTECTest();0 rows inserted/updated/deletedij> -- causes the trigger to fire, which causes a thread-- to be cranked upinsert into t values 1;1 row inserted/updated/deletedij> -- tell the background thread that dml is done,-- it will now try to do some stuff with the stale-- tec. We MUST do this in a different thread lest-- we block the background thread on connection -- synchronizationconnect 'wombat' as conn2;ij(CONN2)> call app.notifyDMLDone();...nested thread running using expired tec...trying to loop through stale result setGot expected exception: SQL Exception: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.Got expected exception: java.sql.SQLException: Statement closed.Got expected exception: java.sql.SQLException: Statement closed.0 rows inserted/updated/deletedij(CONN2)> disconnect;ij> set connection connection0;ij> -- Test for bug 3495 - triggers were causing deferred insert, which-- caused the insert to use a TemporaryRowHolderImpl. This was not-- being re-initialized properly when closed, and it was trying to-- re-insert the row from the first insert.autocommit off;ij> drop table t;0 rows inserted/updated/deletedij> create table t (x int);0 rows inserted/updated/deletedij> create trigger tr after insert on t for each statement mode db2sql values 1;0 rows inserted/updated/deletedij> prepare ps as 'insert into t values (?)';ij> execute ps using 'values (1)';1 row inserted/updated/deletedij> execute ps using 'values (2)';1 row inserted/updated/deletedij> select * from t;X -----------1 2 ij> -- Test MODE DB2SQL not as reserved keyword. beetle 4546 drop table db2sql;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'DB2SQL' because it does not exist.ij> drop table db2sql2;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'DB2SQL2' because it does not exist.ij> create table db2sql (db2sql int, mode int, yipng int);0 rows inserted/updated/deletedij> create table db2sql2 (db2sql2 int);0 rows inserted/updated/deletedij> -- Test MODE DB2SQL on trigger. beetle 4546drop trigger db2sqltr1;ERROR 42X94: TRIGGER 'DB2SQLTR1' does not exist.ij> create trigger db2sqltr1 after insert on db2sql for each rowMODE DB2SQL insert into db2sql2 values (1);0 rows inserted/updated/deletedij> -- Test optimizer plan of trigger action. Beetle 4826autocommit on;ij> drop table parent;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'PARENT' because it does not exist.ij> create table t1(a int not null primary key, b int);0 rows inserted/updated/deletedij> create table parent (a int not null primary key, b int);0 rows inserted/updated/deletedij> create trigger trig1 AFTER DELETE on t1referencing OLD as OLD for each row mode db2sqldelete from parent where a = OLD.a;0 rows inserted/updated/deletedij> insert into t1 values (0, 1);1 row inserted/updated/deletedij> insert into t1 values (1, 1);1 row inserted/updated/deletedij> insert into t1 values (2, 1);1 row inserted/updated/deletedij> insert into t1 values (3, 1);1 row inserted/updated/deletedij> insert into parent values (0, 1);1 row inserted/updated/deletedij> insert into parent values (1, 1);1 row inserted/updated/deletedij> insert into parent values (2, 1);1 row inserted/updated/deletedij> insert into parent values (3, 1);1 row inserted/updated/deletedij> insert into parent values (4, 1);1 row inserted/updated/deletedij> autocommit off ;ij> delete from t1 where a = 3;1 row inserted/updated/deletedij> select type, mode, tablename from new org.apache.derby.diag.LockTable() t order by tablename, type;TYPE |MODE|TABLENAME -------------------------------------------------------------------------------------------------------------------------------------------ROW |X |PARENT TABLE|IX |PARENT ROW |X |T1 TABLE|IX |T1 ij> rollback;ij> autocommit on;ij> drop table t1;0 rows inserted/updated/deletedij> drop table parent;0 rows inserted/updated/deletedij> -- Test use of old AND new referencing names within the same trigger (beetle 5725).create table x(x int);0 rows inserted/updated/deletedij> insert into x values (2), (8), (78);3 rows inserted/updated/deletedij> create table removed (x int);0 rows inserted/updated/deletedij> -- statement triggercreate trigger t1 after update of x on x referencing old_table as old new_table as new for each statement mode db2sql insert into removed select * from old where x not in (select x from new where x < 10);0 rows inserted/updated/deletedij> select * from x;X -----------2 8 78 ij> select * from removed;X -----------ij> update x set x=18 where x=8;1 row inserted/updated/deletedij> select * from x;X -----------2 18 78 ij> select * from removed;X -----------8 ij> -- row triggercreate trigger t2 after update of x on x referencing old as oldrow new as newrow for each row mode db2sql insert into removed values (newrow.x + oldrow.x);0 rows inserted/updated/deletedij> update x set x=28 where x=18;1 row inserted/updated/deletedij> select * from x;X -----------2 28 78 ij> select * from removed;X -----------8 18 46 ij> -- do an alter table, then make sure triggers recompile correctly.alter table x add column y int;0 rows inserted/updated/deletedij> update x set x=88 where x > 44;1 row inserted/updated/deletedij> select * from x;X |Y -----------------------2 |NULL 28 |NULL 88 |NULL ij> select * from removed;X -----------8 18 46 78 166 ij> drop table x;0 rows inserted/updated/deletedij> drop table removed;0 rows inserted/updated/deletedij> create table x (x int, constraint ck check (x > 0));0 rows inserted/updated/deletedij> -- aftercreate trigger tgood after insert on x for each statement mode db2sql insert into x values 666;0 rows inserted/updated/deletedij> insert into x values 1;1 row inserted/updated/deletedij> select * from x;X -----------1 666 ij> drop trigger tgood;0 rows inserted/updated/deletedij> create trigger tgood after insert on x for each statement mode db2sql delete from x;0 rows inserted/updated/deletedij> insert into x values 1;1 row inserted/updated/deletedij> select * from x;X -----------ij> drop trigger tgood;0 rows inserted/updated/deletedij> create trigger tgood after insert on x for each statement mode db2sql update x set x = x+100;0 rows inserted/updated/deletedij> insert into x values 1;1 row inserted/updated/deletedij> select * from x;X -----------101 ij> drop trigger tgood;0 rows inserted/updated/deletedij> delete from x;1 row inserted/updated/deletedij> drop table x;0 rows inserted/updated/deletedij> -- Derby-388: When a set of inserts/updates is performed on a table-- and each update fires a trigger that in turn performs other updates,-- Derby will sometimes try to recompile the trigger in the middle-- of the update process and will throw an NPE when doing so.create procedure d388 () language java parameter style java modifies sql data external name 'org.apache.derbyTesting.functionTests.tests.lang.userDefMethods.derby388';0 rows inserted/updated/deletedij> -- Just call the procedure; it will do the rest.call d388();Running DERBY-388 Test.DERBY-388 Test Passed.0 rows inserted/updated/deletedij> -- Derby-85: It turns out that if a table t1 exists in a non-default schema -- and the default schema (e.g., "SOMEUSER") doesn't exist yet (because no -- objects have been created in that schema), then attempts to create a -- trigger on t1 using its qualified name will lead to a null pointer -- exception in the Derby engine. connect 'wombat;user=someuser';ij(CONNECTION1)> autocommit off;ij(CONNECTION1)> create table myschema.mytable (i int);0 rows inserted/updated/deletedij(CONNECTION1)> create trigger mytrigger after update on myschema.mytable for each row mode db2sql select * from sys.systables;0 rows inserted/updated/deletedij(CONNECTION1)> rollback;ij(CONNECTION1)> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -