You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by be...@apache.org on 2006/10/30 10:34:11 UTC
svn commit: r469102 [4/4] - in /db/derby/code/trunk/java: demo/toursdb/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/master/DerbyNet/
testing/org/apache/derbyTesting/functionTests/master/DerbyNet/j...
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions.sql Mon Oct 30 01:34:06 2006
@@ -145,7 +145,7 @@
create trigger trig_delete after DELETE on t2
referencing old as deletedrow
-for each row mode db2sql
+for each row
insert into t4 values(deletedrow.x , 'bd');
insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;
@@ -313,7 +313,7 @@
create trigger trig_update after UPDATE on t2
referencing old as updatedrow
-for each row mode db2sql
+for each row
insert into t4 values(updatedrow.x , 'bu');
insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;
@@ -381,7 +381,7 @@
create table t2(x int references t1(a) ON DELETE NO ACTION , y int);
create trigger trig_delete after DELETE on t1
referencing old as deletedrow
-for each row mode db2sql
+for each row
delete from t2 where x = deletedrow.a;
insert into t1 values(1 , 2);
@@ -412,7 +412,7 @@
create table t1(a int not null unique, b int not null unique);
create table t2(x int references t1(a) ON UPDATE NO ACTION , y int);
create trigger trig_update after UPDATE on t1
-referencing old as old for each row mode db2sql
+referencing old as old for each row
update t2 set x = 2 where x = old.a;
insert into t1 values(1 , 2);
@@ -456,7 +456,7 @@
create trigger trig_delete after DELETE on t2
referencing old as deletedrow
-for each row mode db2sql
+for each row
insert into t4 values(deletedrow.x , 'ad');
insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;
@@ -485,7 +485,7 @@
create trigger trig_delete after DELETE on t2
REFERENCING OLD_Table AS deletedrows
-for each statement mode db2sql
+for each statement
insert into t4 select x, 'ad' from deletedrows;
insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;
@@ -523,7 +523,7 @@
create trigger trig_emp_delete after DELETE on emp
REFERENCING OLD_Table AS deletedrows
-for each statement mode db2sql
+for each statement
insert into tempemp select empno, mgr, 'ad' from deletedrows;
delete from emp where empno = 'e1';
@@ -554,7 +554,7 @@
create trigger trig_emp_delete AFTER DELETE on emp
REFERENCING OLD_Table AS deletedrows
-for each statement mode db2sql
+for each statement
insert into tempemp select empno, mgr, 'bd' from deletedrows;
delete from emp where empno = 'e1';
@@ -595,7 +595,7 @@
create trigger trig_cyclic_del after DELETE on t1
REFERENCING OLD_Table AS deletedrows
-for each statement mode db2sql
+for each statement
insert into t1temp select a, b, 'ad' from deletedrows;
select * from t1;
@@ -646,7 +646,7 @@
create trigger trig_cyclic_del AFTER DELETE on t1
REFERENCING OLD_Table AS deletedrows
-for each statement mode db2sql
+for each statement
insert into t1temp select a, b, 'bd' from deletedrows;
select * from t1;
@@ -699,12 +699,12 @@
create trigger trig_cyclic_del1 after DELETE on t1
referencing old as deletedrow
-for each row mode db2sql
+for each row
insert into t1temp values(deletedrow.a , deletedrow.b, 'ad');
create trigger trig_cyclic_del2 AFTER DELETE on t1
referencing old as deletedrow
-for each row mode db2sql
+for each row
insert into t1temp values(deletedrow.a , deletedrow.b, 'bd');
select * from t1;
@@ -745,12 +745,12 @@
create trigger trig_emp_delete AFTER UPDATE on emp
REFERENCING OLD_Table AS updatedrows
-for each statement mode db2sql
+for each statement
insert into tempemp select empno, mgr, 'bu' from updatedrows;
create trigger trig_emp_delete1 AFTER UPDATE on emp
REFERENCING NEW_Table AS updatedrows
-for each statement mode db2sql
+for each statement
insert into tempemp select empno, mgr, 'au' from updatedrows;
delete from emp where empno = 'e1';
@@ -781,12 +781,12 @@
create trigger trig_emp_delete after UPDATE on emp
REFERENCING OLD AS updatedrow
-for each row mode db2sql
+for each row
insert into tempemp values(updatedrow.empno, updatedrow.mgr, 'bu');
create trigger trig_emp_delete1 AFTER UPDATE on emp
REFERENCING NEW AS updatedrow
-for each row mode db2sql
+for each row
insert into tempemp values(updatedrow.empno, updatedrow.mgr, 'au');
delete from emp where empno = 'e1';
@@ -1180,12 +1180,12 @@
create trigger trig_delete after DELETE on t1
referencing old as deletedrow
-for each row mode db2sql
+for each row
delete from t2;
create trigger trig_delete1 after DELETE on t2
referencing old as deletedrow
-for each row mode db2sql
+for each row
delete from t3;
insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) , (10,11,12),
@@ -1222,12 +1222,12 @@
create trigger trig_delete after DELETE on t1
referencing old as deletedrow
-for each row mode db2sql
+for each row
delete from t2;
create trigger trig_delete1 after DELETE on t2
referencing old as deletedrow
-for each row mode db2sql
+for each row
delete from t3;
insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) , (10,11,12),
@@ -1316,7 +1316,7 @@
create table t4(c1 char (20));
create trigger trig_delete after DELETE on t3
-for each statement mode db2sql
+for each statement
insert into t4 values('ad');
delete from t1 ;
@@ -1324,7 +1324,7 @@
drop trigger trig_delete;
delete from t4 ;
create trigger trig_delete after DELETE on t3
-for each statement mode db2sql
+for each statement
insert into t4 values('bd');
delete from t1 ;
delete from t1 ;
@@ -1336,7 +1336,7 @@
create table t4(z int not null primary key , op char(2));
create trigger trig_delete after DELETE on t3
referencing old as deletedrow
-for each row mode db2sql
+for each row
insert into t4 values(deletedrow.c1 , 'bd');
delete from t1 ;
delete from t1 ;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/releaseCompileLocks.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/releaseCompileLocks.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/releaseCompileLocks.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/releaseCompileLocks.sql Mon Oct 30 01:34:06 2006
@@ -177,9 +177,9 @@
commit;
-- create some triggers.
-create trigger update_of_t after update on t for each row mode db2sql values 2;
+create trigger update_of_t after update on t for each row values 2;
-create trigger insert_of_t after insert on t for each row mode db2sql values 3;
+create trigger insert_of_t after insert on t for each row values 3;
commit;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/renameTable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/renameTable.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/renameTable.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/renameTable.sql Mon Oct 30 01:34:06 2006
@@ -91,7 +91,7 @@
create table t6 (c61 int default 1);
create table t7(c71 int);
-- bug 5684
-create trigger t7insert after insert on t7 referencing new as NEWROW for each row mode db2sql insert into t6 values(NEWROW.c71);
+create trigger t7insert after insert on t7 referencing new as NEWROW for each row insert into t6 values(NEWROW.c71);
insert into t7 values(1);
-- bug 5683. Should fail
rename table t7 to t7r;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql Mon Oct 30 01:34:06 2006
@@ -229,7 +229,7 @@
-- Negative tests. Disable use of schemas starting with SYS
set schema app;
create table t1 (c1 int);
-create trigger sysblah.trig1 after update of c1 on t1 for each row mode db2sql insert into t1 values 1;
+create trigger sysblah.trig1 after update of c1 on t1 for each row insert into t1 values 1;
create procedure sysblah.dummy() language java external name 'NotReallyThere.NoMethod' parameter style java;
drop table t1;
@@ -527,7 +527,7 @@
select * from A5146.V;
drop view A5146.V;
-prepare PS5146_TRIGGER as 'create trigger A5146.DT_TRIG AFTER INSERT ON B5146.DT FOR EACH STATEMENT MODE DB2SQL UPDATE B5146.DT SET I = I + 1';
+prepare PS5146_TRIGGER as 'create trigger A5146.DT_TRIG AFTER INSERT ON B5146.DT FOR EACH STATEMENT UPDATE B5146.DT SET I = I + 1';
drop schema A5146 restrict;
execute PS5146_TRIGGER;
drop trigger A5146.DT_TRIG;
@@ -555,7 +555,7 @@
drop view ISC.V;
drop schema ISC restrict;
-prepare ISC_TRIGGER as 'create trigger ISC.DT_TRIG AFTER INSERT ON B5146.DT FOR EACH STATEMENT MODE DB2SQL UPDATE B5146.DT SET I = I + 1';
+prepare ISC_TRIGGER as 'create trigger ISC.DT_TRIG AFTER INSERT ON B5146.DT FOR EACH STATEMENT UPDATE B5146.DT SET I = I + 1';
execute ISC_TRIGGER;
insert into B5146.DT values 999;
drop trigger ISC.DT_TRIG;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/streams.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/streams.java?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/streams.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/streams.java Mon Oct 30 01:34:06 2006
@@ -98,7 +98,7 @@
stmt.executeUpdate("create table t2 (id int, pid int, lvc long varchar, lvb long varchar for bit data)"),
0);
verifyCount(
- stmt.executeUpdate("create trigger tr21 after insert on t2 for each statement mode db2sql values 1"),
+ stmt.executeUpdate("create trigger tr21 after insert on t2 for each statement values 1"),
0);
verifyCount(
stmt.executeUpdate("create table t3 (id int not null primary key, pid int, lvc long varchar, lvb long varchar for bit data, CONSTRAINT FK1 Foreign Key(pid) REFERENCES T3 (id))"),
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql Mon Oct 30 01:34:06 2006
@@ -128,14 +128,14 @@
create table table2 (i int, j int);
-- Should fail
-create trigger tins after insert on myTable referencing new_table as new for each statement mode db2sql insert into table2 select i,j from table1;
+create trigger tins after insert on myTable referencing new_table as new for each statement insert into table2 select i,j from table1;
-- Should pass
-create trigger tins after insert on table1 referencing new_table as new for each statement mode db2sql insert into table2 select i,j from table1;
+create trigger tins after insert on table1 referencing new_table as new for each statement insert into table2 select i,j from table1;
drop trigger tins;
-create trigger triggerins after insert on table2 referencing new_table as new for each statement mode db2sql insert into myTable select i,j from new;
+create trigger triggerins after insert on table2 referencing new_table as new for each statement insert into myTable select i,j from new;
select * from myTable;
insert into table2 values (5, 5);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/syscat.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/syscat.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/syscat.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/syscat.sql Mon Oct 30 01:34:06 2006
@@ -165,7 +165,7 @@
from sys.sysconglomerates
where conglomeratename = 'DECIMAL_TAB_IDX' order by conglomeratename;
-create trigger t1 after update on decimal_tab for each row mode db2sql values 1;
+create trigger t1 after update on decimal_tab for each row values 1;
-- referenced columns.
select triggername, referencedcolumns
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerBeforeTrig.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerBeforeTrig.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerBeforeTrig.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerBeforeTrig.sql Mon Oct 30 01:34:06 2006
@@ -26,28 +26,28 @@
------------------------------------
-- DDL
------------------------------------
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql drop table x;
+create trigger tbad NO CASCADE before insert on x for each statement drop table x;
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql drop index x;
+create trigger tbad NO CASCADE before insert on x for each statement drop index x;
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql alter table x add column y int;
+create trigger tbad NO CASCADE before insert on x for each statement alter table x add column y int;
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql alter table x add constraint ck2 check(x > 0);
+create trigger tbad NO CASCADE before insert on x for each statement alter table x add constraint ck2 check(x > 0);
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql alter table x drop constraint ck;
+create trigger tbad NO CASCADE before insert on x for each statement alter table x drop constraint ck;
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql create index x2 on x (x);
+create trigger tbad NO CASCADE before insert on x for each statement create index x2 on x (x);
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql create index xunrelated on unrelated(x);
+create trigger tbad NO CASCADE before insert on x for each statement create index xunrelated on unrelated(x);
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql drop index xunrelated;
+create trigger tbad NO CASCADE before insert on x for each statement drop index xunrelated;
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql drop trigger tbad;
+create trigger tbad NO CASCADE before insert on x for each statement drop trigger tbad;
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql
- create trigger tbad2 NO CASCADE before insert on x for each statement mode db2sql values 1;
+create trigger tbad NO CASCADE before insert on x for each statement
+ create trigger tbad2 NO CASCADE before insert on x for each statement values 1;
-create trigger tokv1 NO CASCADE before insert on x for each statement mode db2sql values 1;
+create trigger tokv1 NO CASCADE before insert on x for each statement values 1;
insert into x values 1;
select * from x;
drop trigger tokv1;
@@ -55,9 +55,9 @@
------------------------------------
-- MISC
------------------------------------
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql set isolation to rr;
+create trigger tbad NO CASCADE before insert on x for each statement set isolation to rr;
-create trigger tbad NO CASCADE before insert on x for each statement mode db2sql lock table x in share mode;
+create trigger tbad NO CASCADE before insert on x for each statement lock table x in share mode;
------------------------------------
-- DML, cannot perform dml on same
@@ -65,38 +65,38 @@
-- after
------------------------------------
-- before
-create trigger tbadX NO CASCADE before insert on x for each statement mode db2sql insert into x values 1;
+create trigger tbadX NO CASCADE before insert on x for each statement insert into x values 1;
-create trigger tbadX NO CASCADE before insert on x for each statement mode db2sql delete from x;
+create trigger tbadX NO CASCADE before insert on x for each statement delete from x;
-create trigger tbadX NO CASCADE before insert on x for each statement mode db2sql update x set x = x;
+create trigger tbadX NO CASCADE before insert on x for each statement update x set x = x;
-- Following tests moved here from triggerRefClause, since these use BEFORE triggers
-- syntax
-create trigger t1 NO CASCADE before update on x referencing badtoken as oldtable for each row mode db2sql values 1;
-create trigger t1 NO CASCADE before update on x referencing old as oldrow new for each row mode db2sql values 1;
+create trigger t1 NO CASCADE before update on x referencing badtoken as oldtable for each row values 1;
+create trigger t1 NO CASCADE before update on x referencing old as oldrow new for each row values 1;
-- dup names
create trigger t1 NO CASCADE before update on x referencing old as oldrow new as newrow old as oldrow2
- for each row mode db2sql values 1;
+ for each row values 1;
create trigger t1 NO CASCADE before update on x referencing new as newrow new as newrow2 old as oldrow2
- for each row mode db2sql values 1;
+ for each row values 1;
--- mismatch: row->for each statement mode db2sql, table->for each row
-create trigger t1 NO CASCADE before update on x referencing new_table as newtab for each row mode db2sql values 1;
-create trigger t1 NO CASCADE before update on x referencing new as newrow for each statement mode db2sql values 1;
+-- mismatch: row->for each statement, table->for each row
+create trigger t1 NO CASCADE before update on x referencing new_table as newtab for each row values 1;
+create trigger t1 NO CASCADE before update on x referencing new as newrow for each statement values 1;
-- same as above, but using old
-create trigger t1 NO CASCADE before update on x referencing old_table as old for each row mode db2sql select * from old;
-create trigger t1 NO CASCADE before update on x referencing old_table as old for each statement mode db2sql values old.x;
+create trigger t1 NO CASCADE before update on x referencing old_table as old for each row select * from old;
+create trigger t1 NO CASCADE before update on x referencing old_table as old for each statement values old.x;
-- old and new cannot be used once they have been redefined
-create trigger t1 NO CASCADE before update on x referencing old_table as oldtable for each statement mode db2sql select * from old;
-create trigger t1 NO CASCADE before update on x referencing old as oldtable for each row mode db2sql values old.x;
+create trigger t1 NO CASCADE before update on x referencing old_table as oldtable for each statement select * from old;
+create trigger t1 NO CASCADE before update on x referencing old as oldtable for each row values old.x;
-- try some other likely uses
create table y (x int);
-create trigger t1 NO CASCADE before insert on x referencing new_table as newrowtab for each statement mode db2sql insert into y select x from newrowtab;
+create trigger t1 NO CASCADE before insert on x referencing new_table as newrowtab for each statement insert into y select x from newrowtab;
drop table x;
drop table y;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql Mon Oct 30 01:34:06 2006
@@ -28,12 +28,12 @@
create view v as select * from x;
-- ok
-create trigger t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;
+create trigger t1 NO CASCADE before update of x,y on x for each row values 1;
-- trigger already exists
-create trigger t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;
+create trigger t1 NO CASCADE before update of x,y on x for each row values 1;
-- trigger already exists
-create trigger app.t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;
+create trigger app.t1 NO CASCADE before update of x,y on x for each row values 1;
-- make sure system tables look as we expect
select cast(triggername as char(10)), event, firingtime, type, state, referencedcolumns from sys.systriggers;
@@ -48,45 +48,45 @@
drop trigger t1;
-- not in sys schema
-create trigger sys.tr NO CASCADE before insert on x for each row mode db2sql values 1;
+create trigger sys.tr NO CASCADE before insert on x for each row values 1;
-- not on table in sys schema
-create trigger tr NO CASCADE before insert on sys.systables for each row mode db2sql values 1;
+create trigger tr NO CASCADE before insert on sys.systables for each row values 1;
-- duplicate columns, not allowed
-create trigger tr NO CASCADE before update of x, x on x for each row mode db2sql values 1;
+create trigger tr NO CASCADE before update of x, x on x for each row values 1;
-- no params in column list
-create trigger tr NO CASCADE before update of x, ? on x for each row mode db2sql values 1;
+create trigger tr NO CASCADE before update of x, ? on x for each row values 1;
-- invalid column
-create trigger tr NO CASCADE before update of doesnotexist on x for each row mode db2sql values 1;
+create trigger tr NO CASCADE before update of doesnotexist on x for each row values 1;
-- not on view
-create trigger tr NO CASCADE before insert on v for each row mode db2sql values 1;
+create trigger tr NO CASCADE before insert on v for each row values 1;
-- error to use table qualifier
-create trigger tr NO CASCADE before update of x.x on x for each row mode db2sql values 1;
+create trigger tr NO CASCADE before update of x.x on x for each row values 1;
-- 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;
+create trigger tr NO CASCADE before update of app.x.x on x for each row values 1;
-- 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 = ?;
+create trigger tr NO CASCADE before delete on x for each row select * from x where x = ?;
-create trigger stmttrigger NO CASCADE before delete on x for each statement mode db2sql values 1;
+create trigger stmttrigger NO CASCADE before delete on x for each statement values 1;
select triggername, type from sys.systriggers where triggername = 'STMTTRIGGER';
drop trigger stmttrigger;
-create trigger rowtrigger NO CASCADE before delete on x for each row mode db2sql values 1;
+create trigger rowtrigger NO CASCADE before delete on x for each row values 1;
select triggername, type from sys.systriggers where triggername = 'ROWTRIGGER';
drop trigger rowtrigger;
-- 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;
+create trigger t2 NO CASCADE before update of x,y on x for each row values 1;
-- CREATE CONSTRAINT
alter table x add constraint ck2 check(x > 0);
@@ -112,7 +112,7 @@
-- MAKE SURE TRIGGER SPS IS RECOMPILED IF TABLE IS ALTERED.
create table y (x int, y int, z int);
-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;
+create trigger tins after insert on x referencing new_table as newtab for each statement insert into y select x, y, z from newtab;
insert into x values (1, 1, 1);
alter table x add column w int default 100;
@@ -124,9 +124,9 @@
-- 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;
-create trigger t3 after update of x,y on x for each statement mode db2sql values 1;
-create trigger t4 after delete on x for each statement mode db2sql values 1;
+create trigger t2 NO CASCADE before update of x,y on x for each row values 1;
+create trigger t3 after update of x,y on x for each statement values 1;
+create trigger t4 after delete on x for each statement values 1;
select cast(triggername as char(10)), tablename from sys.systriggers t, sys.systables tb
where t.tableid = tb.tableid order by 1;
drop view v;
@@ -140,10 +140,10 @@
create table x (x int, y int, z int);
create schema test;
-create trigger test.t1 NO CASCADE before delete on x for each row mode db2sql values 1;
+create trigger test.t1 NO CASCADE before delete on x for each row values 1;
set schema test;
-create trigger t2 NO CASCADE before delete on app.x for each row mode db2sql values 1;
+create trigger t2 NO CASCADE before delete on app.x for each row values 1;
select schemaname, triggername from sys.systriggers t, sys.sysschemas s
where s.schemaid = t.schemaid;
@@ -169,15 +169,15 @@
--
-- Test trigger firing order
--
-create trigger t1 after insert on t for each row mode db2sql
+create trigger t1 after insert on t for each row
values app.triggerFiresMin('3rd');
-create trigger t2 after insert on t for each statement mode db2sql
+create trigger t2 after insert on t for each statement
values app.triggerFiresMin('1st');
-create trigger t3 no cascade before insert on t for each row mode db2sql
+create trigger t3 no cascade before insert on t for each row
values app.triggerFiresMin('4th');
-create trigger t4 after insert on t for each row mode db2sql
+create trigger t4 after insert on t for each row
values app.triggerFiresMin('2nd');
-create trigger t5 no cascade before insert on t for each statement mode db2sql
+create trigger t5 no cascade before insert on t for each statement
values app.triggerFiresMin('5th');
insert into t values (1,1,'1');
drop trigger t1;
@@ -188,15 +188,15 @@
-- 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
+create trigger t1 after insert on t for each row
values app.triggerFires('3rd');
-create trigger t2 no cascade before insert on t for each statement mode db2sql
+create trigger t2 no cascade before insert on t for each statement
values app.triggerFires('1st');
-create trigger t3 after insert on t for each row mode db2sql
+create trigger t3 after insert on t for each row
values app.triggerFires('4th');
-create trigger t4 no cascade before insert on t for each row mode db2sql
+create trigger t4 no cascade before insert on t for each row
values app.triggerFires('2nd');
-create trigger t5 after insert on t for each statement mode db2sql
+create trigger t5 after insert on t for each statement
values app.triggerFires('5th');
insert into t values
(2,2,'2'),
@@ -215,25 +215,25 @@
-- statement triggers fire, row triggers
-- do not.
--
-create trigger t1 after insert on t for each row mode db2sql
+create trigger t1 after insert on t for each row
values app.triggerFires('ROW: empty insert, should NOT fire');
-create trigger t2 after insert on t for each statement mode db2sql
+create trigger t2 after insert on t for each statement
values app.triggerFires('STATEMENT: empty insert, ok');
insert into t select * from t;
drop trigger t1;
drop trigger t2;
-create trigger t1 after update on t for each row mode db2sql
+create trigger t1 after update on t for each row
values app.triggerFires('ROW: empty update, should NOT fire');
-create trigger t2 after update on t for each statement mode db2sql
+create trigger t2 after update on t for each statement
values app.triggerFires('STATEMENT: empty update, ok');
update t set x = x;
drop trigger t1;
drop trigger t2;
-create trigger t1 after delete on t for each row mode db2sql
+create trigger t1 after delete on t for each row
values app.triggerFires('ROW: empty delete, should NOT fire');
-create trigger t2 after delete on t for each statement mode db2sql
+create trigger t2 after delete on t for each statement
values app.triggerFires('STATEMENT: empty delete, ok');
delete from t;
drop trigger t1;
@@ -248,9 +248,9 @@
create table f (x int,
constraint ck check (x > 0),
constraint fk foreign key (x) references p);
-create trigger t1 no cascade before insert on f for each row mode db2sql
+create trigger t1 no cascade before insert on f for each row
values app.triggerFiresMin('BEFORE constraints');
-create trigger t2 after insert on f for each row mode db2sql
+create trigger t2 after insert on f for each row
values app.triggerFiresMin('AFTER constraints');
-- INSERT
@@ -276,9 +276,9 @@
-- UPDATE
-create trigger t1 no cascade before update on f for each row mode db2sql
+create trigger t1 no cascade before update on f for each row
values app.triggerFiresMin('BEFORE constraints');
-create trigger t2 after update on f for each row mode db2sql
+create trigger t2 after update on f for each row
values app.triggerFiresMin('AFTER constraints');
-- fails, ck violated
@@ -303,9 +303,9 @@
-- DELETE
insert into f values 1;
-create trigger t1 no cascade before delete on p for each row mode db2sql
+create trigger t1 no cascade before delete on p for each row
values app.triggerFiresMin('BEFORE constraints');
-create trigger t2 after delete on p for each row mode db2sql
+create trigger t2 after delete on p for each row
values app.triggerFiresMin('AFTER constraints');
-- fails, fk violated
@@ -325,17 +325,17 @@
--
drop table t;
create table t (c1 int, c2 int);
-create trigger tins after insert on t for each row mode db2sql
+create trigger tins after insert on t for each row
values app.triggerFiresMin('insert');
-create trigger tdel after delete on t for each row mode db2sql
+create trigger tdel after delete on t for each row
values app.triggerFiresMin('delete');
-create trigger tupc1 after update of c1 on t for each row mode db2sql
+create trigger tupc1 after update of c1 on t for each row
values app.triggerFiresMin('update c1');
-create trigger tupc2 after update of c2 on t for each row mode db2sql
+create trigger tupc2 after update of c2 on t for each row
values app.triggerFiresMin('update c2');
-create trigger tupc1c2 after update of c1,c2 on t for each row mode db2sql
+create trigger tupc1c2 after update of c1,c2 on t for each row
values app.triggerFiresMin('update c1,c2');
-create trigger tupc2c1 after update of c2,c1 on t for each row mode db2sql
+create trigger tupc2c1 after update of c2,c1 on t for each row
values app.triggerFiresMin('update c2,c1');
insert into t values (1,1);
update t set c1 = 1;
@@ -350,7 +350,7 @@
create table trighistory("cOlUmN1" int, "cOlUmN2 " int, "cOlUmN3"""" " int);
insert into trigtable values (1, 2, 3);
create trigger "tt1" after insert on trigtable
-referencing NEW as NEW for each row mode db2sql
+referencing NEW as NEW for each row
insert into trighistory ("cOlUmN1", "cOlUmN2 ", "cOlUmN3"""" ") values (new."cOlUmN1" + 5, "NEW"."cOlUmN2 " * new."cOlUmN3"""" ", 5);
maximumdisplaywidth 2000;
select cast(triggername as char(10)), CAST (TRIGGERDEFINITION AS VARCHAR(180)), STMTNAME from sys.systriggers t, sys.sysstatements s
@@ -359,7 +359,7 @@
select * from trighistory;
drop trigger "tt1";
create trigger "tt1" after insert on trigtable
-referencing new as new for each row mode db2sql
+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);
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';
@@ -374,7 +374,7 @@
create table trighistory(trigtable char(30), c1 int, c2 int);
create trigger trigtable1 after update on trigtable1
referencing OLD as oldtable
-for each row mode db2sql
+for each row
insert into trighistory values ('trigtable1', oldtable.c1, oldtable.c2);
insert into trigtable1 values (1, 1);
update trigtable1 set c1 = 11, c2 = 11;
@@ -389,7 +389,7 @@
autocommit off;
drop table t;
create table t (x int);
-create trigger tr after insert on t for each statement mode db2sql values 1;
+create trigger tr after insert on t for each statement values 1;
prepare ps as 'insert into t values (?)';
execute ps using 'values (1)';
execute ps using 'values (2)';
@@ -416,7 +416,7 @@
create table parent (a int not null primary key, b int);
create trigger trig1 AFTER DELETE on t1
-referencing OLD as OLD for each row mode db2sql
+referencing OLD as OLD for each row
delete from parent where a = OLD.a;
insert into t1 values (0, 1);
@@ -446,7 +446,7 @@
-- 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
+ 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);
@@ -458,7 +458,7 @@
-- 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
+ old as oldrow new as newrow for each row insert into
removed values (newrow.x + oldrow.x);
update x set x=28 where x=18;
@@ -478,24 +478,24 @@
create table x (x int, constraint ck check (x > 0));
-- after
-create trigger tgood after insert on x for each statement mode db2sql insert into x values 666;
+create trigger tgood after insert on x for each statement insert into x values 666;
insert into x values 1;
select * from x;
drop trigger tgood;
-create trigger tgood after insert on x for each statement mode db2sql delete from x;
+create trigger tgood after insert on x for each statement delete from x;
insert into x values 1;
select * from x;
drop trigger tgood;
-create trigger tgood after insert on x for each statement mode db2sql update x set x = x+100;
+create trigger tgood after insert on x for each statement update x set x = x+100;
insert into x values 1;
select * from x;
drop trigger tgood;
delete from x;
create trigger tgood after insert on x
-for each statement mode db2sql insert into x values (666), (999), (333);
+for each statement insert into x values (666), (999), (333);
insert into x values 1;
select * from x order by 1;
drop trigger tgood;
@@ -503,7 +503,7 @@
create trigger tgood after insert on x
referencing new as n
-for each row mode db2sql insert into x values (n.x);
+for each row insert into x values (n.x);
insert into x values 7;
select * from x order by 1;
drop trigger tgood;
@@ -511,7 +511,7 @@
create trigger tgood after insert on x
referencing new as n
-for each row mode db2sql insert into x values (333), (999), (333);
+for each row insert into x values (333), (999), (333);
insert into x values 1;
select * from x order by 1;
drop trigger tgood;
@@ -521,7 +521,7 @@
-- fail. Enable this trigger test case to resolve 1204.
-- create trigger tgood after insert on x
-- referencing new as n
--- for each row mode db2sql insert into x values (n.x), (999), (333);
+-- for each row insert into x values (n.x), (999), (333);
-- insert into x values 1;
-- select * from x order by 1;
-- drop trigger tgood;
@@ -547,7 +547,7 @@
connect 'wombat;user=someuser';
autocommit off;
create table myschema.mytable (i int);
-create trigger mytrigger after update on myschema.mytable for each row mode db2sql select * from sys.systables;
+create trigger mytrigger after update on myschema.mytable for each row select * from sys.systables;
rollback;
disconnect;
@@ -558,7 +558,7 @@
create table t438_t (id int, bl blob, l int, nc decimal(6,2), oc decimal(6,2));
create trigger tr_438 after update on t438
referencing new as n old as o
-for each row mode db2sql
+for each row
insert into t438_t(id, bl, l, nc, oc) values (n.id, n.bl, length(n.bl), n.cost, o.cost);
-- initially just some small BLOB values.
@@ -581,7 +581,7 @@
create table t438_t (id int, cl clob, l int, nc decimal(6,2), oc decimal(6,2));
create trigger tr_438 after update on t438
referencing new as n old as o
-for each row mode db2sql
+for each row
insert into t438_t(id, cl, l, nc, oc) values (n.id, n.cl, length(n.cl), n.cost, o.cost);
-- initially just some small CLOB values.
@@ -604,7 +604,7 @@
create table tsn_t (a integer, b integer);
create trigger tr_sn after insert on tsn
referencing new as n
-for each row mode db2sql
+for each row
insert into tsn_t(a, b) values (n.I, n."i");
insert into tsn values (1, 234);
select * from tsn;
@@ -623,12 +623,12 @@
CREATE TRIGGER TRIGGER_T10641
AFTER DELETE ON T10641
REFERENCING OLD AS OLD_ROW
- FOR EACH ROW MODE DB2SQL
+ FOR EACH ROW
INSERT INTO T10641_DELETIONS VALUES (OLD_ROW.X);
CREATE TRIGGER TRIGGER_T10642
AFTER DELETE ON T10642
REFERENCING OLD AS OLD_ROW
- FOR EACH ROW MODE DB2SQL
+ FOR EACH ROW
INSERT INTO T10642_DELETIONS VALUES (OLD_ROW.Y);
INSERT INTO T10641 VALUES (0);
INSERT INTO T10642 VALUES (0);
@@ -649,7 +649,7 @@
create trigger update_test
after update on test
referencing old as old
- for each row mode db2sql
+ for each row
update test set ts=current_timestamp where testid=old.testid;
insert into test(info) values (1),(2),(3);
UPDATE TEST SET INFO = 1 WHERE TESTID = 2;
@@ -659,7 +659,7 @@
-- creating and dropping index on the table in the trigger action
create table t1 (i int);
create table t2 (i int);
-create trigger tt after insert on t1 for each statement mode db2sql insert into t2 values 1;
+create trigger tt after insert on t1 for each statement insert into t2 values 1;
insert into t1 values 1;
create unique index tu on t2(i);
insert into t1 values 1;
@@ -675,7 +675,7 @@
-- dropping and recreating a table which the trigger references
create table t3 (i int);
create table t4 (i int);
-create trigger tt2 after insert on t3 for each statement mode db2sql insert into t4 values 1;
+create trigger tt2 after insert on t3 for each statement insert into t4 values 1;
insert into t3 values 1;
select * from t4;
drop table t4;
@@ -687,7 +687,7 @@
-- 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';
create table test(a integer);
-create trigger test_trigger AFTER insert on test FOR EACH ROW MODE DB2SQL values max_value(2,4);
+create trigger test_trigger AFTER insert on test FOR EACH ROW values max_value(2,4);
insert into test values(1);
@@ -708,7 +708,7 @@
-- get ready to create a trigger. Trigger is created on t31TriggerTest and it inserts into t32TriggerTest
create table t31TriggerTest (c311 int);
create table t32TriggerTest (c321 int);
-create trigger tr31t31TriggerTest after insert on t31TriggerTest for each statement mode db2sql
+create trigger tr31t31TriggerTest after insert on t31TriggerTest for each statement
insert into t32TriggerTest values (select c111 from v21ViewTest where c112=1);
-- try an insert which will fire the trigger
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRecursion.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRecursion.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRecursion.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRecursion.sql Mon Oct 30 01:34:06 2006
@@ -36,23 +36,23 @@
create table t15 (x int);
create table t16 (x int);
create table t17 (x int);
-create trigger tr1 after insert on t1 for each row MODE DB2SQL insert into t2 values 666;
-create trigger tr2 after insert on t2 for each row MODE DB2SQL insert into t3 values 666;
-create trigger tr3 after insert on t3 for each row MODE DB2SQL insert into t4 values 666;
-create trigger tr4 after insert on t4 for each row MODE DB2SQL insert into t5 values 666;
-create trigger tr5 after insert on t5 for each row MODE DB2SQL insert into t6 values 666;
-create trigger tr6 after insert on t6 for each row MODE DB2SQL insert into t7 values 666;
-create trigger tr7 after insert on t7 for each row MODE DB2SQL insert into t8 values 666;
-create trigger tr8 after insert on t8 for each row MODE DB2SQL insert into t9 values 666;
-create trigger tr9 after insert on t9 for each row MODE DB2SQL insert into t10 values 666;
-create trigger tr10 after insert on t10 for each row MODE DB2SQL insert into t11 values 666;
-create trigger tr11 after insert on t11 for each row MODE DB2SQL insert into t12 values 666;
-create trigger tr12 after insert on t12 for each row MODE DB2SQL insert into t13 values 666;
-create trigger tr13 after insert on t13 for each row MODE DB2SQL insert into t14 values 666;
-create trigger tr14 after insert on t14 for each row MODE DB2SQL insert into t15 values 666;
-create trigger tr15 after insert on t15 for each row MODE DB2SQL insert into t16 values 666;
-create trigger tr16 after insert on t16 for each row MODE DB2SQL insert into t17 values 666;
-create trigger tr17 after insert on t17 for each row MODE DB2SQL values 1;
+create trigger tr1 after insert on t1 for each row insert into t2 values 666;
+create trigger tr2 after insert on t2 for each row insert into t3 values 666;
+create trigger tr3 after insert on t3 for each row insert into t4 values 666;
+create trigger tr4 after insert on t4 for each row insert into t5 values 666;
+create trigger tr5 after insert on t5 for each row insert into t6 values 666;
+create trigger tr6 after insert on t6 for each row insert into t7 values 666;
+create trigger tr7 after insert on t7 for each row insert into t8 values 666;
+create trigger tr8 after insert on t8 for each row insert into t9 values 666;
+create trigger tr9 after insert on t9 for each row insert into t10 values 666;
+create trigger tr10 after insert on t10 for each row insert into t11 values 666;
+create trigger tr11 after insert on t11 for each row insert into t12 values 666;
+create trigger tr12 after insert on t12 for each row insert into t13 values 666;
+create trigger tr13 after insert on t13 for each row insert into t14 values 666;
+create trigger tr14 after insert on t14 for each row insert into t15 values 666;
+create trigger tr15 after insert on t15 for each row insert into t16 values 666;
+create trigger tr16 after insert on t16 for each row insert into t17 values 666;
+create trigger tr17 after insert on t17 for each row values 1;
-- here we go
;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRefClause.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRefClause.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRefClause.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRefClause.sql Mon Oct 30 01:34:06 2006
@@ -29,32 +29,32 @@
-- syntax
-- mismatch: insert->old, delete->new
-create trigger t1 after insert on x referencing old as oldrow for each row mode db2sql values 1;
-create trigger t1 after insert on x referencing old_table as oldtab for each statement mode db2sql values 1;
-create trigger t1 after insert on x referencing old_table as oldtab for each statement mode db2sql values 1;
-create trigger t1 after delete on x referencing new as newrow for each row mode db2sql values 1;
-create trigger t1 after delete on x referencing new_table as newtab for each statement mode db2sql values 1;
-create trigger t1 after delete on x referencing new_table as newtab for each statement mode db2sql values 1;
+create trigger t1 after insert on x referencing old as oldrow for each row values 1;
+create trigger t1 after insert on x referencing old_table as oldtab for each statement values 1;
+create trigger t1 after insert on x referencing old_table as oldtab for each statement values 1;
+create trigger t1 after delete on x referencing new as newrow for each row values 1;
+create trigger t1 after delete on x referencing new_table as newtab for each statement values 1;
+create trigger t1 after delete on x referencing new_table as newtab for each statement values 1;
-- same as above, bug using old/new
-create trigger t1 after insert on x referencing old as old for each row mode db2sql values old.x;
-create trigger t1 after insert on x referencing old_table as old for each statement mode db2sql select * from old;
-create trigger t1 after insert on x referencing old_table as old for each statement mode db2sql select * from old;
-create trigger t1 after delete on x referencing new as new for each row mode db2sql values new.x;
-create trigger t1 after delete on x referencing new_table as new for each statement mode db2sql select * from new;
-create trigger t1 after delete on x referencing new_table as new for each statement mode db2sql select * from new;
+create trigger t1 after insert on x referencing old as old for each row values old.x;
+create trigger t1 after insert on x referencing old_table as old for each statement select * from old;
+create trigger t1 after insert on x referencing old_table as old for each statement select * from old;
+create trigger t1 after delete on x referencing new as new for each row values new.x;
+create trigger t1 after delete on x referencing new_table as new for each statement select * from new;
+create trigger t1 after delete on x referencing new_table as new for each statement select * from new;
-- cannot reference columns that don't exist, not bound as normal stmts
-create trigger t1 after delete on x referencing old as old for each row mode db2sql values old.badcol;
-create trigger t1 after delete on x referencing old as old for each row mode db2sql values old;
-create trigger t1 after delete on x referencing old as oldrow for each row mode db2sql values oldrow.badcol;
-create trigger t1 after delete on x referencing old as oldrow for each row mode db2sql values oldrow;
+create trigger t1 after delete on x referencing old as old for each row values old.badcol;
+create trigger t1 after delete on x referencing old as old for each row values old;
+create trigger t1 after delete on x referencing old as oldrow for each row values oldrow.badcol;
+create trigger t1 after delete on x referencing old as oldrow for each row values oldrow;
-- lets try some basics with old/new table
create table y (x int);
insert into y values 1, 2, 666, 2, 2, 1;
insert into x values (1, null, null), (2, null, null);
-create trigger t1 after delete on x referencing old as old for each row mode db2sql delete from y where x = old.x;
+create trigger t1 after delete on x referencing old as old for each row delete from y where x = old.x;
autocommit off;
delete from x;
@@ -64,7 +64,7 @@
drop trigger t1;
commit;
-create trigger t1 after delete on x referencing old_table as old for each statement mode db2sql delete from y where x in (select x from old);
+create trigger t1 after delete on x referencing old_table as old for each statement delete from y where x in (select x from old);
delete from x;
select * from y;
@@ -92,7 +92,7 @@
dt date, t time, ts timestamp,
b CHAR(2) FOR BIT DATA, bv VARCHAR(2) FOR BIT DATA, lbv LONG VARCHAR FOR BIT DATA,
dc decimal(5,2), n numeric(8,4));
-create trigger t1 after insert on allTypes1 referencing new as newrowtab for each row mode db2sql
+create trigger t1 after insert on allTypes1 referencing new as newrowtab for each row
insert into allTypes2
values (newrowtab.i, newrowtab.tn, newrowtab.s, newrowtab.l,
newrowtab.c, newrowtab.v, newrowtab.lvc,
@@ -135,7 +135,7 @@
create table x (x int);
create table removed (x int);
-- create trigger t1 after update of x on x referencing old_table as old new_table as new
--- for each statement mode db2sql
+-- for each statement
-- insert into removed select * from old where x not in (select x from new where x < 10);
insert into x values 1,3,4,5,6,9,666,667,668;
update x set x = x+1;
@@ -147,7 +147,7 @@
create table x (x int, y int);
create table y (x int, y int);
-create trigger t1 after insert on x referencing new_table as newtab for each statement mode db2sql
+create trigger t1 after insert on x referencing new_table as newtab for each statement
insert into y select newtab.x, y+newtab.y from newtab;
insert into x values (1,1);
select * from y;
@@ -155,7 +155,7 @@
drop trigger t1;
-- how about a correlation of a transition variable
-create trigger t1 after insert on x referencing new_table as newtab for each statement mode db2sql
+create trigger t1 after insert on x referencing new_table as newtab for each statement
insert into y select newtab2.x, y+newtab2.y from newtab newtab2;
insert into x values (1,1);
select * from y;
@@ -171,17 +171,17 @@
create table val (x int);
create table x (b char(5) FOR BIT DATA);
create table y (b char(5) FOR BIT DATA);
-create trigger t1 after insert on x referencing new as new for each row mode db2sql insert into y values (new.b || X'80');
+create trigger t1 after insert on x referencing new as new for each row insert into y values (new.b || X'80');
insert into x values (X'E0');
select * from y;
drop trigger t1;
-create trigger t1 after insert on x referencing new as new for each row mode db2sql insert into y values new.b;
+create trigger t1 after insert on x referencing new as new for each row insert into y values new.b;
insert into x values null;
select * from y;
drop trigger t1;
-create trigger t1 after insert on x referencing new as new for each row mode db2sql insert into val values length(new.b);
+create trigger t1 after insert on x referencing new as new for each row insert into val values length(new.b);
insert into x values X'FFE0';
select * from val;
@@ -199,14 +199,14 @@
create table s_t1(col1 int not null primary key, chgType char(20));
-- should work
-create trigger trig_delete_2 after delete on t1 referencing OLD_TABLE as OLD for each statement mode db2sql
+create trigger trig_delete_2 after delete on t1 referencing OLD_TABLE as OLD for each statement
insert into s_t1 (select col1, 'D'
from OLD where OLD.col1 <> ALL
(select col1 from s_t1 where OLD.col1 = s_t1.col1));
drop trigger trig_delete_2;
-- should work
-create trigger trig_delete_2 after delete on t1 referencing old_table as OLD for each statement mode db2sql
+create trigger trig_delete_2 after delete on t1 referencing old_table as OLD for each statement
insert into s_t1 (select col1, 'D'
from OLD where OLD.col1 <> ALL
(select s_t1.col1 from s_t1, OLD where OLD.col1 = s_t1.col1));
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerStream.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerStream.java?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerStream.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerStream.java Mon Oct 30 01:34:06 2006
@@ -96,17 +96,17 @@
Statement stmt = conn.createStatement();
stmt.execute("create trigger t11 NO CASCADE before update of x,y on x1 "
- + "for each statement mode db2sql values getAsciiColumn( 0, 2, 'a')");
+ + "for each statement values getAsciiColumn( 0, 2, 'a')");
stmt.execute("create trigger t12 after update of x,y on x1 for each row"
- + " mode db2sql values getAsciiColumn( 1, 2, 'a')");
+ + " values getAsciiColumn( 1, 2, 'a')");
stmt.execute("create trigger t13 after insert on x1 for each statement"
- + " mode db2sql values getAsciiColumn( 1, 2, 'a')");
+ + " values getAsciiColumn( 1, 2, 'a')");
stmt.execute("create trigger t14 NO CASCADE before insert on x1 for each row"
- + " mode db2sql values getAsciiColumn( 1, 2, 'a')");
+ + " values getAsciiColumn( 1, 2, 'a')");
stmt.execute("create trigger t15 NO CASCADE before delete on x1 "
- + "for each statement mode db2sql values getAsciiColumn( 0, 2, 'a')");
+ + "for each statement values getAsciiColumn( 0, 2, 'a')");
stmt.execute("create trigger t16 after delete on x1 for each row "
- + "mode db2sql values getAsciiColumn( 0, 2, 'a')");
+ + " values getAsciiColumn( 0, 2, 'a')");
stmt.close();
@@ -139,16 +139,16 @@
Statement stmt = conn.createStatement();
stmt.execute("create trigger t21 NO CASCADE before update of x,y on x2 "
- + "for each statement mode db2sql values getBinaryColumn( 0, 2, 'a')");
+ + "for each statement values getBinaryColumn( 0, 2, 'a')");
stmt.execute("create trigger t22 after update of x,y on x2 for each row"
- + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
+ + " values getBinaryColumn( 1, 2, 'a')");
stmt.execute("create trigger t23 after insert on x2 for each statement"
- + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
+ + " values getBinaryColumn( 1, 2, 'a')");
stmt.execute("create trigger t24 NO CASCADE before insert on x2 for each row"
- + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
+ + " values getBinaryColumn( 1, 2, 'a')");
stmt.execute("create trigger t25 NO CASCADE before delete on x2 for each statement"
- + " mode db2sql values getBinaryColumn( 1, 2, 'a')");
- stmt.execute("create trigger t26 after delete on x2 for each row mode db2sql"
+ + " values getBinaryColumn( 1, 2, 'a')");
+ stmt.execute("create trigger t26 after delete on x2 for each row"
+ " values getBinaryColumn( 0, 2, 'a')");
stmt.close();
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java Mon Oct 30 01:34:06 2006
@@ -3106,12 +3106,12 @@
stmt.executeUpdate("create table table0WithTriggers (c1 int, c2 bigint)");
stmt.executeUpdate("create table deleteTriggerInsertIntoThisTable (c1 int)");
stmt.executeUpdate("create table updateTriggerInsertIntoThisTable (c1 int)");
- stmt.executeUpdate("create trigger tr1 after delete on table0WithTriggers for each statement mode db2sql insert into deleteTriggerInsertIntoThisTable values (1)");
- stmt.executeUpdate("create trigger tr2 after update on table0WithTriggers for each statement mode db2sql insert into updateTriggerInsertIntoThisTable values (1)");
+ stmt.executeUpdate("create trigger tr1 after delete on table0WithTriggers for each statement insert into deleteTriggerInsertIntoThisTable values (1)");
+ stmt.executeUpdate("create trigger tr2 after update on table0WithTriggers for each statement insert into updateTriggerInsertIntoThisTable values (1)");
stmt.executeUpdate("create table table1WithTriggers (c1 int, c2 bigint)");
- stmt.executeUpdate("create trigger tr3 after delete on table1WithTriggers referencing old as old for each row mode db2sql delete from table1WithTriggers where c1=old.c1+1 or c1=old.c1-1");
+ stmt.executeUpdate("create trigger tr3 after delete on table1WithTriggers referencing old as old for each row delete from table1WithTriggers where c1=old.c1+1 or c1=old.c1-1");
stmt.executeUpdate("create table table2WithTriggers (c1 int, c2 bigint)");
- stmt.executeUpdate("create trigger tr4 after update of c2 on table2WithTriggers for each statement mode db2sql update table2WithTriggers set c1=1");
+ stmt.executeUpdate("create trigger tr4 after update of c2 on table2WithTriggers for each statement update table2WithTriggers set c1=1");
stmt.executeUpdate("create table selfReferencingT1 (c1 char(2) not null, c2 char(2), constraint selfReferencingT1 primary key(c1), constraint manages1 foreign key(c2) references selfReferencingT1(c1) on delete cascade)");
stmt.executeUpdate("create table selfReferencingT2 (c1 char(2) not null, c2 char(2), constraint selfReferencingT2 primary key(c1), constraint manages2 foreign key(c2) references selfReferencingT2(c1) on update restrict)");
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/userDefMethods.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/userDefMethods.java?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/userDefMethods.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/userDefMethods.java Mon Oct 30 01:34:06 2006
@@ -95,7 +95,7 @@
s.execute("CREATE TABLE D388_T2 (ID_2 INT)");
s.execute(
"CREATE TRIGGER D388_TRIG1 AFTER UPDATE OF ID ON D388_T1" +
- " REFERENCING NEW AS N_ROW FOR EACH ROW MODE DB2SQL" +
+ " REFERENCING NEW AS N_ROW FOR EACH ROW" +
" UPDATE D388_T2" +
" SET ID_2 = " +
" CASE WHEN (N_ROW.ID <= 0) THEN N_ROW.ID" +
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xml_general.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xml_general.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xml_general.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/xml_general.sql Mon Oct 30 01:34:06 2006
@@ -119,8 +119,8 @@
not logged on commit preserve rows;
-- XML cols can be used in a SET clause, if target value is XML.
-create trigger tr2 after insert on t1 for each row mode db2sql update t1 set x = 'hmm';
-create trigger tr1 after insert on t1 for each row mode db2sql update t1 set x = null;
+create trigger tr2 after insert on t1 for each row update t1 set x = 'hmm';
+create trigger tr1 after insert on t1 for each row update t1 set x = null;
drop trigger tr1;
-- Test XMLPARSE operator.
@@ -719,7 +719,7 @@
create table t10 (i int, x xml);
insert into t9 values (1, xmlparse(document '<name> john </name>' preserve whitespace));
-create trigger tx after insert on t9 for each statement mode db2sql
+create trigger tx after insert on t9 for each statement
insert into t10 values (1, xmlparse(document '<name> jane </name>' preserve whitespace));
insert into t9 values (2, xmlparse(document '<name> ally </name>' preserve whitespace));
select i, xmlserialize(x as varchar(20)) from t9;
@@ -731,7 +731,7 @@
delete from t9;
delete from t10;
insert into t9 values (1, xmlparse(document '<name> john </name>' preserve whitespace));
-create trigger tx after insert on t9 for each statement mode db2sql
+create trigger tx after insert on t9 for each statement
insert into t10 values (1, (select xmlquery('.' passing by ref x returning sequence empty on empty) from t9 where i = 1));
insert into t9 values (2, xmlparse(document '<name> ally </name>' preserve whitespace));
select i, xmlserialize(x as varchar(20)) from t9;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql Mon Oct 30 01:34:06 2006
@@ -156,13 +156,13 @@
-- Triggers
-- ----------------------------------------------
-create trigger trigOne after insert on bar.t3 for each row mode db2sql update bar.t4 set j=8 where i=2;
-create trigger "Foo Bar".trig2 no cascade before delete on bar.t1 for each statement mode db2sql values (1), (2);
-create trigger "TrigThree" after update of i, c on bar."tWithKeys" for each row mode db2sql select c from bar."tWithKeys";
-create trigger bar.reftrig after delete on bar.t8 referencing old_table as oldtable for each statement mode db2sql select * from oldtable;
-create trigger """Quoted""Schema"""."""trig""One""" after insert on """Quoted""Schema"""."tee""""Hee" for each row mode db2sql values(8);
+create trigger trigOne after insert on bar.t3 for each row update bar.t4 set j=8 where i=2;
+create trigger "Foo Bar".trig2 no cascade before delete on bar.t1 for each statement values (1), (2);
+create trigger "TrigThree" after update of i, c on bar."tWithKeys" for each row select c from bar."tWithKeys";
+create trigger bar.reftrig after delete on bar.t8 referencing old_table as oldtable for each statement select * from oldtable;
+create trigger """Quoted""Schema"""."""trig""One""" after insert on """Quoted""Schema"""."tee""""Hee" for each row values(8);
-- Test trigger with new AND old referencing names (beetle 5725).
create table x (x int);
create table removed (x int);
-create trigger trigFour 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);
+create trigger trigFour 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);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql Mon Oct 30 01:34:06 2006
@@ -86,4 +86,4 @@
-- Triggers
-- ----------------------------------------------
-create trigger trigOne after insert on bar.t1 for each row mode db2sql update bar.t1 set i = 4 where i = 2;
+create trigger trigOne after insert on bar.t1 for each row update bar.t1 set i = 4 where i = 2;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql Mon Oct 30 01:34:06 2006
@@ -336,7 +336,7 @@
create table test1(a char(20)) ;
create trigger trig_import after INSERT on alltypes
referencing new as newrow
-for each row mode db2sql
+for each row
insert into test1 values(newrow.chartype);
call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' ,
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeTester.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeTester.java?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeTester.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeTester.java Mon Oct 30 01:34:06 2006
@@ -801,6 +801,9 @@
Statement s = conn.createStatement();
+ boolean modeDb2SqlOptional =
+ dbMajor>10 || (dbMajor == 10 && dbMinor > 2);
+
switch (phase) {
case PH_CREATE:
s.execute("CREATE TABLE D438.T438(a int, b varchar(20), c int)");
@@ -810,12 +813,14 @@
s.execute(
"create trigger D438.T438_ROW_1 after UPDATE on D438.T438 " +
"referencing new as n old as o " +
- "for each row mode db2sql "+
+ "for each row "+
+ (modeDb2SqlOptional?"":"mode db2sql ") +
"insert into D438.T438_T1(a, b) values (n.a, n.b || '_ROW')");
s.executeUpdate(
"create trigger D438.T438_STMT_1 after UPDATE on D438.T438 " +
"referencing new_table as n " +
- "for each statement mode db2sql "+
+ "for each statement "+
+ (modeDb2SqlOptional?"":"mode db2sql ") +
"insert into D438.T438_T1(a, b) select n.a, n.b || '_STMT' from n");
conn.commit();
@@ -825,12 +830,14 @@
s.execute(
"create trigger D438.T438_ROW_2 after UPDATE on D438.T438 " +
"referencing new as n old as o " +
- "for each row mode db2sql "+
+ "for each row "+
+ (modeDb2SqlOptional?"":"mode db2sql ") +
"insert into D438.T438_T2(a, c) values (n.a, n.c + 100)");
s.executeUpdate(
"create trigger D438.T438_STMT_2 after UPDATE on D438.T438 " +
"referencing new_table as n " +
- "for each statement mode db2sql "+
+ "for each statement "+
+ (modeDb2SqlOptional?"":"mode db2sql ") +
"insert into D438.T438_T2(a, c) select n.a, n.c + 4000 from n");
conn.commit();
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/ProcedureTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/ProcedureTest.java?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/ProcedureTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/ProcedureTest.java Mon Oct 30 01:34:06 2006
@@ -101,7 +101,7 @@
public static void createTrigger() throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement ps = conn.prepareStatement("create trigger test_trig" +
- " AFTER delete on t1 for each STATEMENT mode db2sql insert into" +
+ " AFTER delete on t1 for each STATEMENT insert into" +
" t1 values(20, 'twenty')");
ps.execute();
ps.close();
Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java Mon Oct 30 01:34:06 2006
@@ -176,9 +176,6 @@
else
sb.append("ROW ");
- // DB2 requires the following keywords in order to work.
- sb.append("MODE DB2SQL ");
-
// Finally, the trigger action.
sb.append(dblook.removeNewlines(aTrig.getString(8)));
return sb.toString();