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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql select * from x where x = ?; ERROR 42Y27: Parameters are not allowed in the trigger action. ij> create trigger stmttrigger NO CASCADE before delete on x for each statement mode db2sql values 1; 0 rows inserted/updated/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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql values 1; 0 rows inserted/updated/deleted ij> create trigger t3 after update of x,y on x for each statement mode db2sql values 1; 0 rows inserted/updated/deleted ij> create trigger t4 after delete on x for each statement mode db2sql 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 mode db2sql 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 mode db2sql 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> -- -- Test trigger firing order -- create trigger t1 after insert on t for each row mode db2sql values app.triggerFiresMin('3rd'); 0 rows inserted/updated/deleted ij> create trigger t2 after insert on t for each statement mode db2sql values app.triggerFiresMin('1st'); 0 rows inserted/updated/deleted ij> create trigger t3 no cascade before insert on t for each row mode db2sql values app.triggerFiresMin('4th'); 0 rows inserted/updated/deleted ij> create trigger t4 after insert on t for each row mode db2sql values app.triggerFiresMin('2nd'); 0 rows inserted/updated/deleted ij> create trigger t5 no cascade before insert on t for each statement mode db2sql values app.triggerFiresMin('5th'); 0 rows inserted/updated/deleted ij> insert into t values (1,1,'1'); TRIGGER: <4th> TRIGGER: <5th> TRIGGER: <3rd> TRIGGER: <1st> TRIGGER: <2nd> 1 row 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> -- 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 mode db2sql values app.triggerFires('3rd'); 0 rows inserted/updated/deleted ij> create trigger t2 no cascade before insert on t for each statement mode db2sql values app.triggerFires('1st'); 0 rows inserted/updated/deleted ij> create trigger t3 after insert on t for each row mode db2sql values app.triggerFires('4th'); 0 rows inserted/updated/deleted ij> create trigger t4 no cascade before insert on t for each row mode db2sql values app.triggerFires('2nd'); 0 rows inserted/updated/deleted ij> create trigger t5 after insert on t for each statement mode db2sql 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; 4 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> -- -- Test firing on empty change sets, -- statement triggers fire, row triggers -- do not. -- create trigger t1 after insert on t for each row mode db2sql values app.triggerFires('ROW: empty insert, should NOT fire'); 0 rows inserted/updated/deleted ij> create trigger t2 after insert on t for each statement mode db2sql values app.triggerFires('STATEMENT: empty insert, ok'); 0 rows inserted/updated/deleted ij> insert into t select * from t; TRIGGER: on statement insert into t select * from t BEFORE RESULT SET AFTER RESULT SET X,Y,C - - - 0 rows inserted/updated/deleted ij> drop trigger t1; 0 rows inserted/updated/deleted ij> drop trigger t2; 0 rows inserted/updated/deleted ij> create trigger t1 after update on t for each row mode db2sql values app.triggerFires('ROW: empty update, should NOT fire'); 0 rows inserted/updated/deleted ij> create trigger t2 after update on t for each statement mode db2sql values app.triggerFires('STATEMENT: empty update, ok'); 0 rows inserted/updated/deleted ij> update t set x = x; TRIGGER: on statement update t set x = x BEFORE RESULT SET X,Y,C - - - AFTER RESULT SET X,Y,C - - - 0 rows inserted/updated/deleted ij> drop trigger t1; 0 rows inserted/updated/deleted ij> drop trigger t2; 0 rows inserted/updated/deleted ij> create trigger t1 after delete on t for each row mode db2sql values app.triggerFires('ROW: empty delete, should NOT fire'); 0 rows inserted/updated/deleted ij> create trigger t2 after delete on t for each statement mode db2sql values app.triggerFires('STATEMENT: empty delete, ok'); 0 rows inserted/updated/deleted ij> delete from t; TRIGGER: on statement delete from t BEFORE RESULT SET X,Y,C - - - AFTER RESULT SET 0 rows inserted/updated/deleted ij> drop trigger t1; 0 rows inserted/updated/deleted ij> drop trigger t2; 0 rows inserted/updated/deleted ij> drop table x; 0 rows inserted/updated/deleted ij> -- -- Trigger ordering wrt constraints -- create table p (x int not null, constraint pk primary key (x)); 0 rows inserted/updated/deleted ij> insert into p values 1,2,3; 3 rows inserted/updated/deleted ij> create table f (x int, constraint ck check (x > 0), constraint fk foreign key (x) references p); 0 rows inserted/updated/deleted ij> create trigger t1 no cascade before insert on f for each row mode db2sql values app.triggerFiresMin('BEFORE constraints'); 0 rows inserted/updated/deleted ij> create trigger t2 after insert on f for each row mode db2sql values app.triggerFiresMin('AFTER constraints'); 0 rows inserted/updated/deleted ij> -- INSERT -- fails, ck violated insert into f values 0; TRIGGER: ERROR 23513: The check constraint 'CK' was violated while performing an INSERT or UPDATE on table 'APP.F'. ij> alter table f drop constraint ck; 0 rows inserted/updated/deleted ij> -- fails, fk violated insert into f values 0; TRIGGER: ERROR 23503: INSERT on table 'F' caused a violation of foreign key constraint 'FK' for key (0). The statement has been rolled back. ij> alter table f drop foreign key fk; 0 rows inserted/updated/deleted ij> -- ok insert into f values 0; TRIGGER: TRIGGER: 1 row inserted/updated/deleted ij> delete from f; 1 row inserted/updated/deleted ij> alter table f add constraint ck check (x > 0); 0 rows inserted/updated/deleted ij> alter table f add constraint fk foreign key (x) references p; 0 rows inserted/updated/deleted ij> drop trigger t1; 0 rows inserted/updated/deleted ij> drop trigger t2; 0 rows inserted/updated/deleted ij> insert into f values (1); 1 row inserted/updated/deleted ij> -- UPDATE create trigger t1 no cascade before update on f for each row mode db2sql values app.triggerFiresMin('BEFORE constraints'); 0 rows inserted/updated/deleted ij> create trigger t2 after update on f for each row mode db2sql values app.triggerFiresMin('AFTER constraints'); 0 rows inserted/updated/deleted ij> -- fails, ck violated update f set x = 0; TRIGGER: ERROR 23513: The check constraint 'CK' was violated while performing an INSERT or UPDATE on table 'APP.F'. ij> alter table f drop constraint ck; 0 rows inserted/updated/deleted ij> -- fails, fk violated update f set x = 0; TRIGGER: ERROR 23503: UPDATE on table 'F' caused a violation of foreign key constraint 'FK' for key (0). The statement has been rolled back. ij> alter table f drop foreign key fk; 0 rows inserted/updated/deleted ij> -- ok update f set x = 0; TRIGGER: TRIGGER: 1 row inserted/updated/deleted ij> delete from f; 1 row inserted/updated/deleted ij> alter table f add constraint ck check (x > 0); 0 rows inserted/updated/deleted ij> alter table f add constraint fk foreign key (x) references p; 0 rows inserted/updated/deleted ij> drop trigger t1; 0 rows inserted/updated/deleted ij> drop trigger t2; 0 rows inserted/updated/deleted ij> -- DELETE insert into f values 1; 1 row inserted/updated/deleted ij> create trigger t1 no cascade before delete on p for each row mode db2sql values app.triggerFiresMin('BEFORE constraints'); 0 rows inserted/updated/deleted ij> create trigger t2 after delete on p for each row mode db2sql values app.triggerFiresMin('AFTER constraints'); 0 rows inserted/updated/deleted ij> -- fails, fk violated delete from p; TRIGGER: TRIGGER: TRIGGER: ERROR 23503: DELETE on table 'P' caused a violation of foreign key constraint 'FK' for key (1). The statement has been rolled back. ij> alter table f drop foreign key fk; 0 rows inserted/updated/deleted ij> -- ok delete from p; TRIGGER: TRIGGER: TRIGGER: TRIGGER: TRIGGER: TRIGGER: 3 rows inserted/updated/deleted ij> drop table f; 0 rows inserted/updated/deleted ij> drop table p; 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 mode db2sql values app.triggerFiresMin('insert'); 0 rows inserted/updated/deleted ij> create trigger tdel after delete on t for each row mode db2sql values app.triggerFiresMin('delete'); 0 rows inserted/updated/deleted ij> create trigger tupc1 after update of c1 on t for each row mode db2sql values app.triggerFiresMin('update c1'); 0 rows inserted/updated/deleted ij> create trigger tupc2 after update of c2 on t for each row mode db2sql values app.triggerFiresMin('update c2'); 0 rows inserted/updated/deleted ij> create trigger tupc1c2 after update of c1,c2 on t for each row mode db2sql values app.triggerFiresMin('update c1,c2'); 0 rows inserted/updated/deleted ij> create trigger tupc2c1 after update of c2,c1 on t for each row mode db2sql values app.triggerFiresMin('update c2,c1'); 0 rows inserted/updated/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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 MODE DB2SQL 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 MODE DB2SQL 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 mode db2sql 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 mode db2sql 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 mode db2sql 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 MODE DB2SQL 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 mode db2sql 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-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> -- ok create trigger tgood after insert on x referencing new as n for each row mode db2sql insert into x values (n.x), (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> create table x (i int); 0 rows inserted/updated/deleted ij> create table y (i int); 0 rows inserted/updated/deleted ij> -- ok create trigger tgood after insert on x for each statement mode db2sql insert into y values (666), (999), (333); 0 rows inserted/updated/deleted ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> -- ok create trigger tgood after insert on x referencing new as n for each row mode db2sql insert into y values (n.i); 0 rows inserted/updated/deleted ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> -- ok create trigger tgood after insert on x referencing new as n for each row mode db2sql insert into y values (333), (999), (333); 0 rows inserted/updated/deleted ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> -- ok. This used to throw StringIndexOutOfBoundsException create trigger tgood after insert on x referencing new as n for each row mode db2sql insert into y values (n.i), (999), (333); 0 rows inserted/updated/deleted ij> insert into x values (888); 1 row inserted/updated/deleted ij> select * from y; I ----------- 888 999 333 ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> delete from x; 1 row inserted/updated/deleted ij> delete from y; 3 rows inserted/updated/deleted ij> create trigger tgood after insert on x referencing new as n for each row mode db2sql insert into y values (n.i), (n.i+1), (n.i+2); 0 rows inserted/updated/deleted ij> insert into x values (1), (4), (7); 3 rows inserted/updated/deleted ij> select * from y; I ----------- 1 2 3 4 5 6 7 8 9 ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> drop table x; 0 rows inserted/updated/deleted ij> drop table y; 0 rows inserted/updated/deleted ij> create table x (i int, j varchar(10)); 0 rows inserted/updated/deleted ij> create table y (i int, j varchar(10)); 0 rows inserted/updated/deleted ij> create trigger tgood after insert on x referencing new as n for each row mode db2sql insert into y values (0, 'X'), (n.i, 'Y'), (0, n.j), (n.i,n.j); 0 rows inserted/updated/deleted ij> insert into x values (1,'A'), (2,'B'), (3, 'C'); 3 rows inserted/updated/deleted ij> 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> drop trigger tgood; 0 rows inserted/updated/deleted ij> drop table x; 0 rows inserted/updated/deleted ij> drop table y; 0 rows inserted/updated/deleted ij>