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. 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. 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. 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 * 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 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 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. 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)>