ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- -- General trigger test -- 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/deleted ij> 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/deleted ij> 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/deleted ij> create view v as select * from x; 0 rows inserted/updated/deleted ij> -- ok create trigger t1 NO CASCADE before update of x,y on x for each row values 1; 0 rows inserted/updated/deleted ij> -- trigger already exists create trigger t1 NO CASCADE before update of x,y on x for each row values 1; ERROR X0Y32: Trigger 'T1' already exists in Schema 'APP'. ij> -- trigger already exists create trigger app.t1 NO CASCADE before update of x,y on x for each row values 1; ERROR X0Y32: Trigger 'T1' already exists in Schema 'APP'. ij> -- make sure system tables look as we expect select 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/deleted ij> -- not in sys schema create trigger sys.tr NO CASCADE before insert on x for each row values 1; ERROR 42X62: 'CREATE TRIGGER' is not allowed in the 'SYS' schema. ij> -- not on table in sys schema create trigger tr NO CASCADE before insert on sys.systables for each row values 1; ERROR X0Y56: 'CREATE TRIGGER' is not allowed on the System table '"SYS"."SYSTABLES"'. ij> -- duplicate columns, not allowed create trigger tr NO CASCADE before update of x, x on x for each row values 1; ERROR 42Y40: 'X' appears multiple times in the UPDATE OF column list for trigger 'TR'. ij> -- no params in column list create trigger tr NO CASCADE before update of x, ? on x for each row values 1; ERROR 42X01: Syntax error: Encountered "?" at line 2, column 50. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- invalid column create trigger tr NO CASCADE before update of doesnotexist on x for each row values 1; ERROR 42X14: 'DOESNOTEXIST' is not a column in table or VTI 'X'. ij> -- not on view create trigger tr NO CASCADE before insert on v for each row values 1; ERROR 42Y62: 'CREATE TRIGGER' is not allowed on '"APP"."V"' because it is a view. ij> -- error to use table qualifier create trigger tr NO CASCADE before update of x.x on x for each row values 1; ERROR 42X01: Syntax error: Encountered "." at line 2, column 48. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- error to use schema.table qualifier create trigger tr NO CASCADE before update of app.x.x on x for each row values 1; ERROR 42X01: Syntax error: Encountered "." at line 2, column 50. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- no params in trigger action -- bad create trigger tr NO CASCADE before delete on x for each row 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 values 1; 0 rows inserted/updated/deleted ij> select triggername, type from sys.systriggers where triggername = 'STMTTRIGGER'; TRIGGERNAME |& ---------------------------------------------------------------------------------------------------------------------------------- STMTTRIGGER |S ij> drop trigger stmttrigger; 0 rows inserted/updated/deleted ij> create trigger rowtrigger NO CASCADE before delete on x for each row values 1; 0 rows inserted/updated/deleted ij> select triggername, type from sys.systriggers where triggername = 'ROWTRIGGER'; TRIGGERNAME |& ---------------------------------------------------------------------------------------------------------------------------------- ROWTRIGGER |R ij> drop trigger rowtrigger; 0 rows inserted/updated/deleted ij> -- fool around with depedencies -- CREATE TRIGGER create trigger t2 NO CASCADE before update of x,y on x for each row values 1; 0 rows inserted/updated/deleted ij> -- CREATE CONSTRAINT alter table x add constraint ck2 check(x > 0); 0 rows inserted/updated/deleted ij> -- DROP VIEW drop view v; 0 rows inserted/updated/deleted ij> -- CREATE VIEW create view v as select * from x; 0 rows inserted/updated/deleted ij> -- CREATE INDEX create index ix on x(x); 0 rows inserted/updated/deleted ij> -- DROP TRIGGER: to the other types we have here drop trigger t2; 0 rows inserted/updated/deleted ij> -- DROP INDEX drop index ix; 0 rows inserted/updated/deleted ij> -- DROP CONSTRAINT alter table x drop constraint ck2; 0 rows inserted/updated/deleted ij> -- MAKE SURE TRIGGER SPS IS RECOMPILED IF TABLE IS ALTERED. create table y (x int, y int, z int); 0 rows inserted/updated/deleted ij> create trigger tins after insert on x referencing new_table as newtab for each statement insert into y select x, y, z from newtab; 0 rows inserted/updated/deleted ij> insert into x values (1, 1, 1); 1 row inserted/updated/deleted ij> alter table x add column w int default 100; 0 rows inserted/updated/deleted ij> alter table x add constraint nonulls check (w is not null); 0 rows inserted/updated/deleted ij> insert into x values (2, 2, 2, 2); 1 row inserted/updated/deleted ij> select * from y; X |Y |Z ----------------------------------- 1 |1 |1 2 |2 |2 ij> drop trigger tins; 0 rows inserted/updated/deleted ij> drop table y; 0 rows inserted/updated/deleted ij> -- prove that by dropping the underlying table, we have dropped the trigger -- first, lets create a few other triggers create trigger t2 NO CASCADE before update of x,y on x for each row values 1; 0 rows inserted/updated/deleted ij> create trigger t3 after update of x,y on x for each statement values 1; 0 rows inserted/updated/deleted ij> create trigger t4 after delete on x for each statement values 1; 0 rows inserted/updated/deleted ij> 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/deleted ij> drop table x; 0 rows inserted/updated/deleted ij> 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/deleted ij> create schema test; 0 rows inserted/updated/deleted ij> create trigger test.t1 NO CASCADE before delete on x for each row values 1; 0 rows inserted/updated/deleted ij> set schema test; 0 rows inserted/updated/deleted ij> create trigger t2 NO CASCADE before delete on app.x for each row values 1; 0 rows inserted/updated/deleted ij> 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/deleted ij> -- fails drop schema test restrict; ERROR X0Y54: Schema 'TEST' cannot be dropped because it is not empty. ij> drop trigger test.t2; 0 rows inserted/updated/deleted ij> -- fails drop schema test restrict; ERROR X0Y54: Schema 'TEST' cannot be dropped because it is not empty. ij> set schema test; 0 rows inserted/updated/deleted ij> drop trigger t1; 0 rows inserted/updated/deleted ij> set schema app; 0 rows inserted/updated/deleted ij> -- ok this time drop schema test restrict; 0 rows inserted/updated/deleted ij> create table t (x int, y int, c char(1)); 0 rows inserted/updated/deleted ij> -- try multiple values, make sure result sets don't get screwed up -- this time we'll print out result sets create trigger t1 after insert on t for each row values app.triggerFires('3rd'); 0 rows inserted/updated/deleted ij> create trigger t2 no cascade before insert on t for each statement values app.triggerFires('1st'); 0 rows inserted/updated/deleted ij> create trigger t3 after insert on t for each row values app.triggerFires('4th'); 0 rows inserted/updated/deleted ij> create trigger t4 no cascade before insert on t for each row values app.triggerFires('2nd'); 0 rows inserted/updated/deleted ij> create trigger t5 after insert on t for each statement values app.triggerFires('5th'); 0 rows inserted/updated/deleted ij> 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 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 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 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 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 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 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 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 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 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 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 AFTER RESULT SET X,Y,C - - - {2,2,2} {3,3,3} {4,4,4} 3 rows inserted/updated/deleted ij> delete from t; 3 rows inserted/updated/deleted ij> drop trigger t1; 0 rows inserted/updated/deleted ij> drop trigger t2; 0 rows inserted/updated/deleted ij> drop trigger t3; 0 rows inserted/updated/deleted ij> drop trigger t4; 0 rows inserted/updated/deleted ij> drop trigger t5; 0 rows inserted/updated/deleted ij> drop table x; 0 rows inserted/updated/deleted ij> -- -- Prove that we are firing the proper triggers based -- on the columns we are changing; -- drop table t; 0 rows inserted/updated/deleted ij> create table t (c1 int, c2 int); 0 rows inserted/updated/deleted ij> create trigger tins after insert on t for each row values app.triggerFiresMin('insert'); 0 rows inserted/updated/deleted ij> create trigger tdel after delete on t for each row values app.triggerFiresMin('delete'); 0 rows inserted/updated/deleted ij> create trigger tupc1 after update of c1 on t for each row values app.triggerFiresMin('update c1'); 0 rows inserted/updated/deleted ij> create trigger tupc2 after update of c2 on t for each row values app.triggerFiresMin('update c2'); 0 rows inserted/updated/deleted ij> create trigger tupc1c2 after update of c1,c2 on t for each row values app.triggerFiresMin('update c1,c2'); 0 rows inserted/updated/deleted ij> create trigger tupc2c1 after update of c2,c1 on t for each row values app.triggerFiresMin('update c2,c1'); 0 rows inserted/updated/deleted ij> insert into t values (1,1); TRIGGER: 1 row inserted/updated/deleted ij> update t set c1 = 1; TRIGGER: TRIGGER: TRIGGER: 1 row inserted/updated/deleted ij> update t set c2 = 1; TRIGGER: TRIGGER: TRIGGER: 1 row inserted/updated/deleted ij> update t set c2 = 1, c1 = 1; TRIGGER: TRIGGER: TRIGGER: TRIGGER: 1 row inserted/updated/deleted ij> update t set c1 = 1, c2 = 1; TRIGGER: TRIGGER: TRIGGER: TRIGGER: 1 row inserted/updated/deleted ij> delete from t; TRIGGER: 1 row inserted/updated/deleted ij> -- Make sure that triggers work with delimited identifiers -- Make sure that text munging works correctly create table trigtable("cOlUmN1" int, "cOlUmN2 " int, "cOlUmN3"""" " int); 0 rows inserted/updated/deleted ij> create table trighistory("cOlUmN1" int, "cOlUmN2 " int, "cOlUmN3"""" " int); 0 rows inserted/updated/deleted ij> insert into trigtable values (1, 2, 3); 1 row inserted/updated/deleted ij> create trigger "tt1" after insert on trigtable referencing NEW as NEW for each row insert into trighistory ("cOlUmN1", "cOlUmN2 ", "cOlUmN3"""" ") values (new."cOlUmN1" + 5, "NEW"."cOlUmN2 " * new."cOlUmN3"""" ", 5); 0 rows inserted/updated/deleted ij> 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/deleted ij> select * from trighistory; cOlUmN1 |cOlUmN2 |cOlUmN3"" ----------------------------------- 6 |6 |5 ij> drop trigger "tt1"; 0 rows inserted/updated/deleted ij> create trigger "tt1" after insert on trigtable referencing new as new for each row insert into trighistory ("cOlUmN1", "cOlUmN2 ", "cOlUmN3"""" ") values (new."cOlUmN1" + new."cOlUmN1", "NEW"."cOlUmN2 " * new."cOlUmN3"""" ", new."cOlUmN2 " * 3); 0 rows inserted/updated/deleted 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" + 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/deleted ij> select * from trighistory; cOlUmN1 |cOlUmN2 |cOlUmN3"" ----------------------------------- 6 |6 |5 2 |6 |6 ij> drop table trigtable; 0 rows inserted/updated/deleted ij> drop table trighistory; 0 rows inserted/updated/deleted ij> -- trigger bug that got fixed mysteriously -- between xena and buffy create table trigtable1(c1 int, c2 int); 0 rows inserted/updated/deleted ij> create table trighistory(trigtable char(30), c1 int, c2 int); 0 rows inserted/updated/deleted ij> create trigger trigtable1 after update on trigtable1 referencing OLD as oldtable for each row insert into trighistory values ('trigtable1', oldtable.c1, oldtable.c2); 0 rows inserted/updated/deleted ij> insert into trigtable1 values (1, 1); 1 row inserted/updated/deleted ij> update trigtable1 set c1 = 11, c2 = 11; 1 row inserted/updated/deleted ij> select * from trighistory; TRIGTABLE |C1 |C2 ------------------------------------------------------ trigtable1 |1 |1 ij> drop table trigtable1; 0 rows inserted/updated/deleted ij> drop table trighistory; 0 rows inserted/updated/deleted 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/deleted ij> create table t (x int); 0 rows inserted/updated/deleted ij> create trigger tr after insert on t for each statement values 1; 0 rows inserted/updated/deleted ij> prepare ps as 'insert into t values (?)'; ij> execute ps using 'values (1)'; 1 row inserted/updated/deleted ij> execute ps using 'values (2)'; 1 row inserted/updated/deleted ij> 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/deleted ij> create table db2sql2 (db2sql2 int); 0 rows inserted/updated/deleted ij> -- Test MODE DB2SQL on trigger. beetle 4546 drop trigger db2sqltr1; ERROR 42X94: TRIGGER 'DB2SQLTR1' does not exist. ij> create trigger db2sqltr1 after insert on db2sql for each row MODE DB2SQL insert into db2sql2 values (1); 0 rows inserted/updated/deleted ij> -- Test optimizer plan of trigger action. Beetle 4826 autocommit 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/deleted ij> create table parent (a int not null primary key, b int); 0 rows inserted/updated/deleted ij> create trigger trig1 AFTER DELETE on t1 referencing OLD as OLD for each row delete from parent where a = OLD.a; 0 rows inserted/updated/deleted ij> insert into t1 values (0, 1); 1 row inserted/updated/deleted ij> insert into t1 values (1, 1); 1 row inserted/updated/deleted ij> insert into t1 values (2, 1); 1 row inserted/updated/deleted ij> insert into t1 values (3, 1); 1 row inserted/updated/deleted ij> insert into parent values (0, 1); 1 row inserted/updated/deleted ij> insert into parent values (1, 1); 1 row inserted/updated/deleted ij> insert into parent values (2, 1); 1 row inserted/updated/deleted ij> insert into parent values (3, 1); 1 row inserted/updated/deleted ij> insert into parent values (4, 1); 1 row inserted/updated/deleted ij> autocommit off ; ij> delete from t1 where a = 3; 1 row inserted/updated/deleted ij> select type, mode, tablename from syscs_diag.lock_table 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/deleted ij> drop table parent; 0 rows inserted/updated/deleted ij> -- Test use of old AND new referencing names within the same trigger (beetle 5725). create table x(x int); 0 rows inserted/updated/deleted ij> insert into x values (2), (8), (78); 3 rows inserted/updated/deleted ij> create table removed (x int); 0 rows inserted/updated/deleted ij> -- statement trigger create trigger t1 after update of x on x referencing old_table as old new_table as new for each statement insert into removed select x from old where x not in (select x from new where x < 10); 0 rows inserted/updated/deleted ij> 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/deleted ij> select * from x; X ----------- 2 18 78 ij> select * from removed; X ----------- 8 ij> -- row trigger create trigger t2 after update of x on x referencing old as oldrow new as newrow for each row insert into removed values (newrow.x + oldrow.x); 0 rows inserted/updated/deleted ij> update x set x=28 where x=18; 1 row inserted/updated/deleted ij> 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/deleted ij> update x set x=88 where x > 44; 1 row inserted/updated/deleted ij> 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/deleted ij> drop table removed; 0 rows inserted/updated/deleted ij> create table x (x int, constraint ck check (x > 0)); 0 rows inserted/updated/deleted ij> -- after create trigger tgood after insert on x for each statement insert into x values 666; 0 rows inserted/updated/deleted ij> insert into x values 1; ERROR 54038: Maximum depth of nested triggers was exceeded. ij> select * from x; X ----------- ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> create trigger tgood after insert on x for each statement delete from x; 0 rows inserted/updated/deleted ij> insert into x values 1; 1 row inserted/updated/deleted ij> select * from x; X ----------- ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> create trigger tgood after insert on x for each statement update x set x = x+100; 0 rows inserted/updated/deleted ij> insert into x values 1; 1 row inserted/updated/deleted ij> select * from x; X ----------- 101 ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> delete from x; 1 row inserted/updated/deleted ij> create trigger tgood after insert on x for each statement insert into x values (666), (999), (333); 0 rows inserted/updated/deleted ij> insert into x values 1; ERROR 54038: Maximum depth of nested triggers was exceeded. ij> select * from x order by 1; X ----------- ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> delete from x; 0 rows inserted/updated/deleted ij> create trigger tgood after insert on x referencing new as n for each row insert into x values (n.x); 0 rows inserted/updated/deleted ij> insert into x values 7; ERROR 54038: Maximum depth of nested triggers was exceeded. ij> select * from x order by 1; X ----------- ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> delete from x; 0 rows inserted/updated/deleted ij> create trigger tgood after insert on x referencing new as n for each row insert into x values (333), (999), (333); 0 rows inserted/updated/deleted ij> insert into x values 1; ERROR 54038: Maximum depth of nested triggers was exceeded. ij> select * from x order by 1; X ----------- ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> drop table x; 0 rows inserted/updated/deleted ij> -- 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/deleted ij> -- Just call the procedure; it will do the rest. call d388(); Running DERBY-388 Test. DERBY-388 Test Passed. 0 rows inserted/updated/deleted ij> -- 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/deleted ij(CONNECTION1)> create trigger mytrigger after update on myschema.mytable for each row select * from sys.systables; 0 rows inserted/updated/deleted ij(CONNECTION1)> rollback; ij(CONNECTION1)> disconnect; ij> -- DERBY-438 - Working triggers with BLOB columns set connection CONNECTION0; ij> autocommit on; ij> create table t438 (id int, cost decimal(6,2), bl blob); 0 rows inserted/updated/deleted ij> create table t438_t (id int, bl blob, l int, nc decimal(6,2), oc decimal(6,2)); 0 rows inserted/updated/deleted ij> create trigger tr_438 after update on t438 referencing new as n old as o for each row insert into t438_t(id, bl, l, nc, oc) values (n.id, n.bl, length(n.bl), n.cost, o.cost); 0 rows inserted/updated/deleted ij> -- initially just some small BLOB values. insert into t438 values (1, 34.53, cast (X'124594322143423214ab35f2e34c' as blob)); 1 row inserted/updated/deleted ij> insert into t438 values (0, 95.32, null); 1 row inserted/updated/deleted ij> insert into t438 values (2, 22.21, cast (X'aa' as blob)); 1 row inserted/updated/deleted ij> select id, cost, length(bl) from t438 order by 1; ID |COST |3 -------------------------------- 0 |95.32 |NULL 1 |34.53 |14 2 |22.21 |1 ij> update t438 set cost = cost + 1.23; 3 rows inserted/updated/deleted ij> select id, length(bl), l, nc, oc from t438_t order by 1,5,4; ID |2 |L |NC |OC ----------------------------------------------------- 0 |NULL |NULL |96.55 |95.32 1 |14 |14 |35.76 |34.53 2 |1 |1 |23.44 |22.21 ij> select id, cast (bl as blob(20)) from t438 order by 1; ID |2 ---------------------------------------------------- 0 |NULL 1 |124594322143423214ab35f2e34c 2 |aa ij> select id, cast (bl as blob(20)) from t438_t order by 1; ID |2 ---------------------------------------------------- 0 |NULL 1 |124594322143423214ab35f2e34c 2 |aa ij> drop table t438; 0 rows inserted/updated/deleted ij> drop table t438_t; 0 rows inserted/updated/deleted ij> -- now re-start with CLOB types create table t438 (id int, cost decimal(6,2), cl clob); 0 rows inserted/updated/deleted ij> create table t438_t (id int, cl clob, l int, nc decimal(6,2), oc decimal(6,2)); 0 rows inserted/updated/deleted ij> create trigger tr_438 after update on t438 referencing new as n old as o for each row insert into t438_t(id, cl, l, nc, oc) values (n.id, n.cl, length(n.cl), n.cost, o.cost); 0 rows inserted/updated/deleted ij> -- initially just some small CLOB values. insert into t438 values (1, 34.53, cast ('Italy''s centre-left leader Romano Prodi insists his poll victory is valid as contested ballots are checked.' as clob)); 1 row inserted/updated/deleted ij> insert into t438 values (0, 95.32, null); 1 row inserted/updated/deleted ij> insert into t438 values (2, 22.21, cast ('free' as clob)); 1 row inserted/updated/deleted ij> select id, cost, length(cl) from t438 order by 1; ID |COST |3 -------------------------------- 0 |95.32 |NULL 1 |34.53 |107 2 |22.21 |4 ij> update t438 set cost = cost + 1.23; 3 rows inserted/updated/deleted ij> select id, length(cl), l, nc, oc from t438_t order by 1,5,4; ID |2 |L |NC |OC ----------------------------------------------------- 0 |NULL |NULL |96.55 |95.32 1 |107 |107 |35.76 |34.53 2 |4 |4 |23.44 |22.21 ij> select id, cast (cl as clob(60)) from t438 order by 1; ID |2 ------------------------------------------------------------------------ 0 |NULL WARNING 01004: Data truncation 1 |Italy's centre-left leader Romano Prodi insists his poll vic 2 |free ij> select id, cast (cl as clob(60)) from t438_t order by 1; ID |2 ------------------------------------------------------------------------ 0 |NULL WARNING 01004: Data truncation 1 |Italy's centre-left leader Romano Prodi insists his poll vic 2 |free ij> drop table t438; 0 rows inserted/updated/deleted ij> drop table t438_t; 0 rows inserted/updated/deleted ij> -- Testcase showing DERBY-1258 create table tsn (I integer, "i" integer); 0 rows inserted/updated/deleted ij> create table tsn_t (a integer, b integer); 0 rows inserted/updated/deleted ij> create trigger tr_sn after insert on tsn referencing new as n for each row insert into tsn_t(a, b) values (n.I, n."i"); 0 rows inserted/updated/deleted ij> insert into tsn values (1, 234); 1 row inserted/updated/deleted ij> select * from tsn; I |i ----------------------- 1 |234 ij> -- Should have 1,234 as data in tsn_t select * from tsn_t; A |B ----------------------- 1 |234 ij> drop table tsn; 0 rows inserted/updated/deleted ij> drop table tsn_t; 0 rows inserted/updated/deleted ij> -- Testcase showing DERBY-1064 CREATE TABLE T10641 ( X INT PRIMARY KEY ); 0 rows inserted/updated/deleted ij> CREATE TABLE T10641_DELETIONS ( X INT ); 0 rows inserted/updated/deleted ij> CREATE TABLE T10642 ( Y INT, CONSTRAINT Y_AND_X FOREIGN KEY(Y) REFERENCES T10641(X) ON DELETE CASCADE); 0 rows inserted/updated/deleted ij> CREATE TABLE T10642_DELETIONS ( Y INT ); 0 rows inserted/updated/deleted ij> CREATE TRIGGER TRIGGER_T10641 AFTER DELETE ON T10641 REFERENCING OLD AS OLD_ROW FOR EACH ROW INSERT INTO T10641_DELETIONS VALUES (OLD_ROW.X); 0 rows inserted/updated/deleted ij> CREATE TRIGGER TRIGGER_T10642 AFTER DELETE ON T10642 REFERENCING OLD AS OLD_ROW FOR EACH ROW INSERT INTO T10642_DELETIONS VALUES (OLD_ROW.Y); 0 rows inserted/updated/deleted ij> INSERT INTO T10641 VALUES (0); 1 row inserted/updated/deleted ij> INSERT INTO T10642 VALUES (0); 1 row inserted/updated/deleted ij> INSERT INTO T10641 VALUES (1); 1 row inserted/updated/deleted ij> INSERT INTO T10642 VALUES (1); 1 row inserted/updated/deleted ij> SELECT * FROM T10641; X ----------- 0 1 ij> SELECT * FROM T10642; Y ----------- 0 1 ij> DELETE FROM T10641; 2 rows inserted/updated/deleted ij> SELECT * FROM T10641; X ----------- ij> SELECT * FROM T10642; Y ----------- ij> SELECT * FROM T10641_DELETIONS; X ----------- 0 1 ij> SELECT * FROM T10642_DELETIONS; Y ----------- 0 1 ij> -- DERBY-1652 create table test (testid integer not null generated always as identity (start with 1, increment by 1), info integer not null, ts timestamp not null default '1980-01-01-00.00.00.000000'); 0 rows inserted/updated/deleted ij> create trigger update_test after update on test referencing old as old for each row update test set ts=current_timestamp where testid=old.testid; 0 rows inserted/updated/deleted ij> insert into test(info) values (1),(2),(3); 3 rows inserted/updated/deleted ij> UPDATE TEST SET INFO = 1 WHERE TESTID = 2; ERROR 54038: Maximum depth of nested triggers was exceeded. ij> drop table test; 0 rows inserted/updated/deleted ij> -- DERBY-1621 -- creating and dropping index on the table in the trigger action create table t1 (i int); 0 rows inserted/updated/deleted ij> create table t2 (i int); 0 rows inserted/updated/deleted ij> create trigger tt after insert on t1 for each statement insert into t2 values 1; 0 rows inserted/updated/deleted ij> insert into t1 values 1; 1 row inserted/updated/deleted ij> create unique index tu on t2(i); 0 rows inserted/updated/deleted ij> insert into t1 values 1; ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'TU' defined on 'T2'. ij> select * from t2; I ----------- 1 ij> insert into t1 values 1; ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'TU' defined on 'T2'. ij> select * from t2; I ----------- 1 ij> drop index tu; 0 rows inserted/updated/deleted ij> select * from t2; I ----------- 1 ij> insert into t1 values 1; 1 row inserted/updated/deleted ij> select * from t2; I ----------- 1 1 ij> drop trigger tt; 0 rows inserted/updated/deleted ij> -- dropping and recreating a table which the trigger references create table t3 (i int); 0 rows inserted/updated/deleted ij> create table t4 (i int); 0 rows inserted/updated/deleted ij> create trigger tt2 after insert on t3 for each statement insert into t4 values 1; 0 rows inserted/updated/deleted ij> insert into t3 values 1; 1 row inserted/updated/deleted ij> select * from t4; I ----------- 1 ij> drop table t4; 0 rows inserted/updated/deleted ij> insert into t3 values 1; ERROR 42X05: Table/View 'T4' does not exist. ij> create table t4 (i int); 0 rows inserted/updated/deleted ij> insert into t3 values 1; 1 row inserted/updated/deleted ij> select * from t4; I ----------- 1 ij> -- dropping a function which the trigger references create function max_value(x int, y int) returns int language java parameter style java external name 'java.lang.Math.max'; 0 rows inserted/updated/deleted ij> create table test(a integer); 0 rows inserted/updated/deleted ij> create trigger test_trigger AFTER insert on test FOR EACH ROW values max_value(2,4); 0 rows inserted/updated/deleted ij> insert into test values(1); 1 row inserted/updated/deleted ij> --- drop function and again do inserts. these should not work as the trigger would be invalid drop function max_value; 0 rows inserted/updated/deleted ij> insert into test values(2); ERROR 42Y03: 'MAX_VALUE' is not recognized as a function or procedure. ij> insert into test values(1); ERROR 42Y03: 'MAX_VALUE' is not recognized as a function or procedure. ij> -- dropping a view which the trigger references create table t11TriggerTest (c111 int not null primary key, c112 int); 0 rows inserted/updated/deleted ij> insert into t11TriggerTest values(1,1); 1 row inserted/updated/deleted ij> insert into t11TriggerTest values(2,2); 1 row inserted/updated/deleted ij> -- create a view based on table t11TriggerTest create view v21ViewTest as select * from t11TriggerTest; 0 rows inserted/updated/deleted ij> -- get ready to create a trigger. Trigger is created on t31TriggerTest and it inserts into t32TriggerTest create table t31TriggerTest (c311 int); 0 rows inserted/updated/deleted ij> create table t32TriggerTest (c321 int); 0 rows inserted/updated/deleted ij> create trigger tr31t31TriggerTest after insert on t31TriggerTest for each statement insert into t32TriggerTest values (select c111 from v21ViewTest where c112=1); 0 rows inserted/updated/deleted ij> -- try an insert which will fire the trigger insert into t31TriggerTest values(1); 1 row inserted/updated/deleted ij> select * from t31TriggerTest; C311 ----------- 1 ij> -- we know the trigger got fired if there is one row in t32TriggerTest select * from t32TriggerTest; C321 ----------- 1 ij> -- drop the view used by the trigger. drop view v21ViewTest; 0 rows inserted/updated/deleted ij> -- try an insert which would cause insert trigger to fire. The insert trigger should have failed because view doesn't -- exist anymore. insert into t31TriggerTest values(1); ERROR 42X05: Table/View 'V21VIEWTEST' does not exist. ij> select * from t31TriggerTest; C311 ----------- 1 ij> select * from t32TriggerTest; C321 ----------- 1 ij> -- DERBY-630 -- NPE in CREATE TRIGGER when compilation schema is other than APP. connect 'jdbc:derby:wombat;create=true;user=user1;password=pwd' as user1; WARNING 01J01: Database 'wombat' not created, connection made to existing database instead. ij(USER1)> create table ippo.t1 (i int); 0 rows inserted/updated/deleted ij(USER1)> create table ippo.t2 (i int); 0 rows inserted/updated/deleted ij(USER1)> create index ippo.idx2 on t2(i); 0 rows inserted/updated/deleted ij(USER1)> create trigger ippo.tr1 after insert on ippo.t1 for each row mode db2sql insert into ippo.t2 values 1; 0 rows inserted/updated/deleted ij(USER1)> insert into ippo.t1 values 1; 1 row inserted/updated/deleted ij(USER1)> insert into ippo.t1 values 1; 1 row inserted/updated/deleted ij(USER1)> select * from ippo.t2; I ----------- 1 1 ij(USER1)> drop index ippo.idx2; 0 rows inserted/updated/deleted ij(USER1)> insert into ippo.t1 values 1; 1 row inserted/updated/deleted ij(USER1)> select * from ippo.t2; I ----------- 1 1 1 ij(USER1)> drop trigger ippo.tr1; 0 rows inserted/updated/deleted ij(USER1)> drop table ippo.t2; 0 rows inserted/updated/deleted ij(USER1)> drop table ippo.t1; 0 rows inserted/updated/deleted ij(USER1)> drop schema ippo restrict; 0 rows inserted/updated/deleted ij(USER1)> -- DERBY-1953 -- if neither FOR EACH STATEMENT or FOR EACH ROW is specified, FOR EACH STATEMENT is implicit. create table topt1 (i int); 0 rows inserted/updated/deleted ij(USER1)> insert into topt1 values 1,2,3; 3 rows inserted/updated/deleted ij(USER1)> create table topt2 (i int); 0 rows inserted/updated/deleted ij(USER1)> -- expect error create trigger tropt after insert on topt1 for each mode db2sql insert into topt2 values 1; ERROR 42X01: Syntax error: Encountered "mode" at line 2, column 53. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij(USER1)> -- ok create trigger tropt after insert on topt1 insert into topt2 values 1; 0 rows inserted/updated/deleted ij(USER1)> insert into topt1 values 4,5,6; 3 rows inserted/updated/deleted ij(USER1)> -- expect 1 row select * from topt2; I ----------- 1 ij(USER1)> drop trigger tropt; 0 rows inserted/updated/deleted ij(USER1)> delete from topt2; 1 row inserted/updated/deleted ij(USER1)> create trigger tropt after insert on topt1 referencing new_table as new_opt1 insert into topt2 select * from new_opt1; 0 rows inserted/updated/deleted ij(USER1)> insert into topt1 values 7,8,9; 3 rows inserted/updated/deleted ij(USER1)> -- expect 3 rows select * from topt2; I ----------- 7 8 9 ij(USER1)> drop trigger tropt; 0 rows inserted/updated/deleted ij(USER1)> delete from topt2; 3 rows inserted/updated/deleted ij(USER1)> create trigger tropt after insert on topt1 referencing new_table as new_opt1 for each statement insert into topt2 select * from new_opt1; 0 rows inserted/updated/deleted ij(USER1)> insert into topt1 values 10,11,12; 3 rows inserted/updated/deleted ij(USER1)> -- expect 3 rows select * from topt2; I ----------- 10 11 12 ij(USER1)> drop trigger tropt; 0 rows inserted/updated/deleted ij(USER1)> delete from topt2; 3 rows inserted/updated/deleted ij(USER1)> create trigger tropt after update on topt1 referencing old as oldrow for each row insert into topt2 values oldrow.i; 0 rows inserted/updated/deleted ij(USER1)> update topt1 set i=100; 12 rows inserted/updated/deleted ij(USER1)> -- expect 12 rows select * from topt2; I ----------- 1 2 3 4 5 6 7 8 9 10 11 12 ij(USER1)> drop trigger tropt; 0 rows inserted/updated/deleted ij(USER1)> drop table topt2; 0 rows inserted/updated/deleted ij(USER1)> drop table topt1; 0 rows inserted/updated/deleted ij(USER1)> -- DERBY-1204 -- trigger causes StringIndexOutOfBoundsException -- which half closes connection and causes rest of test to -- fail. Enable this trigger test case to resolve 1204. create table x (x int); 0 rows inserted/updated/deleted ij(USER1)> -- ok create trigger tgood after insert on x referencing new as n for each row insert into x values (n.x), (999), (333); 0 rows inserted/updated/deleted ij(USER1)> insert into x values 1; ERROR 54038: Maximum depth of nested triggers was exceeded. ij(USER1)> select * from x order by 1; X ----------- ij(USER1)> drop trigger tgood; 0 rows inserted/updated/deleted ij(USER1)> drop table x; 0 rows inserted/updated/deleted ij(USER1)> create table x (i int); 0 rows inserted/updated/deleted ij(USER1)> create table y (i int); 0 rows inserted/updated/deleted ij(USER1)> -- ok create trigger tgood after insert on x for each statement insert into y values (666), (999), (333); 0 rows inserted/updated/deleted ij(USER1)> drop trigger tgood; 0 rows inserted/updated/deleted ij(USER1)> -- ok create trigger tgood after insert on x referencing new as n for each row insert into y values (n.i); 0 rows inserted/updated/deleted ij(USER1)> drop trigger tgood; 0 rows inserted/updated/deleted ij(USER1)> -- ok create trigger tgood after insert on x referencing new as n for each row insert into y values (333), (999), (333); 0 rows inserted/updated/deleted ij(USER1)> drop trigger tgood; 0 rows inserted/updated/deleted ij(USER1)> -- ok. This used to throw StringIndexOutOfBoundsException create trigger tgood after insert on x referencing new as n for each row insert into y values (n.i), (999), (333); 0 rows inserted/updated/deleted ij(USER1)> insert into x values (888); 1 row inserted/updated/deleted ij(USER1)> select * from y; I ----------- 888 999 333 ij(USER1)> drop trigger tgood; 0 rows inserted/updated/deleted ij(USER1)> delete from x; 1 row inserted/updated/deleted ij(USER1)> delete from y; 3 rows inserted/updated/deleted ij(USER1)> create trigger tgood after insert on x referencing new as n for each row insert into y values (n.i), (n.i+1), (n.i+2); 0 rows inserted/updated/deleted ij(USER1)> insert into x values (1), (4), (7); 3 rows inserted/updated/deleted ij(USER1)> select * from y; I ----------- 1 2 3 4 5 6 7 8 9 ij(USER1)> drop trigger tgood; 0 rows inserted/updated/deleted ij(USER1)> drop table x; 0 rows inserted/updated/deleted ij(USER1)> drop table y; 0 rows inserted/updated/deleted ij(USER1)> create table x (i int, j varchar(10)); 0 rows inserted/updated/deleted ij(USER1)> create table y (i int, j varchar(10)); 0 rows inserted/updated/deleted ij(USER1)> create trigger tgood after insert on x referencing new as n for each row insert into y values (0, 'X'), (n.i, 'Y'), (0, n.j), (n.i,n.j); 0 rows inserted/updated/deleted ij(USER1)> insert into x values (1,'A'), (2,'B'), (3, 'C'); 3 rows inserted/updated/deleted ij(USER1)> select * from y; I |J ---------------------- 0 |X 1 |Y 0 |A 1 |A 0 |X 2 |Y 0 |B 2 |B 0 |X 3 |Y 0 |C 3 |C ij(USER1)> drop trigger tgood; 0 rows inserted/updated/deleted ij(USER1)> drop table x; 0 rows inserted/updated/deleted ij(USER1)> drop table y; 0 rows inserted/updated/deleted ij(USER1)> -- DERBY-2183 -- trigger recompilation test disconnect user1; ij> connect 'jdbc:derby:wombat' user 'user1' as user1; ij(USER1)> set schema app; 0 rows inserted/updated/deleted ij(USER1)> drop trigger app.tr1; ERROR 42X94: TRIGGER 'APP.TR1' does not exist. ij(USER1)> drop table app.t1; 0 rows inserted/updated/deleted ij(USER1)> create table app.t1 (i int, j int); 0 rows inserted/updated/deleted ij(USER1)> insert into app.t1 values (1,10); 1 row inserted/updated/deleted ij(USER1)> create trigger app.tr1 after update of i on app.t1 referencing old as old for each row update t1 set j = old.j+1; 0 rows inserted/updated/deleted ij(USER1)> update app.t1 set i=i+1; 1 row inserted/updated/deleted ij(USER1)> select * from app.t1; I |J ----------------------- 2 |11 ij(USER1)> call sqlj.install_jar('file:dcl_emc1.jar', 'APP.dcl_emc1', 0); 0 rows inserted/updated/deleted ij(USER1)> connect 'jdbc:derby:wombat' user 'user2' as user2; ij(USER2)> -- ok update app.t1 set i=i+1; 1 row inserted/updated/deleted ij(USER2)> select * from app.t1; I |J ----------------------- 3 |12 ij(USER2)> call sqlj.replace_jar('file:dcl_emc1.jar', 'APP.dcl_emc1'); 0 rows inserted/updated/deleted ij(USER2)> update app.t1 set i=i+1; 1 row inserted/updated/deleted ij(USER2)> select * from app.t1; I |J ----------------------- 4 |13 ij(USER2)> call sqlj.remove_jar('APP.dcl_emc1', 0); 0 rows inserted/updated/deleted ij(USER2)> update app.t1 set i=i+1; 1 row inserted/updated/deleted ij(USER2)> select * from app.t1; I |J ----------------------- 5 |14 ij(USER2)> drop trigger app.tr1; 0 rows inserted/updated/deleted ij(USER2)> drop table app.t1; 0 rows inserted/updated/deleted ij(USER2)> set connection user1; ij(USER1)> set schema app; 0 rows inserted/updated/deleted ij(USER1)> create table app.t1 (id int, i int, j int); 0 rows inserted/updated/deleted ij(USER1)> insert into app.t1 values (1,10, 100); 1 row inserted/updated/deleted ij(USER1)> insert into app.t1 values (2,20, 200); 1 row inserted/updated/deleted ij(USER1)> insert into app.t1 values (3,30, 300); 1 row inserted/updated/deleted ij(USER1)> create trigger app.tr1 after update on app.t1 referencing old as oldt new as newt for each row update t1 set t1.j = CASE WHEN (oldt.j < 100) THEN (oldt.j + 1) ELSE 1 END WHERE ((newt.j is null) OR (oldt.j = newt.j)) AND newt.id = t1.id; 0 rows inserted/updated/deleted ij(USER1)> update app.t1 set i=i+1; 3 rows inserted/updated/deleted ij(USER1)> select * from app.t1; ID |I |J ----------------------------------- 1 |11 |1 2 |21 |1 3 |31 |1 ij(USER1)> call sqlj.install_jar('file:dcl_emc1.jar', 'APP.dcl_emc1', 0); 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- ok update app.t1 set i=i+1; 3 rows inserted/updated/deleted ij(USER2)> select * from app.t1; ID |I |J ----------------------------------- 1 |12 |2 2 |22 |2 3 |32 |2 ij(USER2)> call sqlj.replace_jar('file:dcl_emc1.jar', 'APP.dcl_emc1'); 0 rows inserted/updated/deleted ij(USER2)> update app.t1 set i=i+1; 3 rows inserted/updated/deleted ij(USER2)> select * from app.t1; ID |I |J ----------------------------------- 1 |13 |3 2 |23 |3 3 |33 |3 ij(USER2)> call sqlj.remove_jar('APP.dcl_emc1', 0); 0 rows inserted/updated/deleted ij(USER2)> update app.t1 set i=i+1; 3 rows inserted/updated/deleted ij(USER2)> select * from app.t1; ID |I |J ----------------------------------- 1 |14 |4 2 |24 |4 3 |34 |4 ij(USER2)> drop trigger app.tr1; 0 rows inserted/updated/deleted ij(USER2)> drop table app.t1; 0 rows inserted/updated/deleted ij(USER2)>