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 [3/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/master/triggerGeneral.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out Mon Oct 30 01:34:06 2006
@@ -30,13 +30,13 @@
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;
+create trigger t1 NO CASCADE before update of x,y on x for each row values 1;
0 rows inserted/updated/deleted
ij> -- trigger already exists
-create trigger t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;
+create trigger t1 NO CASCADE before update of x,y on x for each row values 1;
ERROR X0Y32: Trigger 'T1' already exists in Schema 'APP'.
ij> -- trigger already exists
-create trigger app.t1 NO CASCADE before update of x,y on x for each row mode db2sql values 1;
+create trigger app.t1 NO CASCADE before update of x,y on x for each row values 1;
ERROR X0Y32: Trigger 'T1' already exists in Schema 'APP'.
ij> -- make sure system tables look as we expect
select cast(triggername as char(10)), event, firingtime, type, state, referencedcolumns from sys.systriggers;
@@ -60,34 +60,34 @@
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;
+create trigger sys.tr NO CASCADE before insert on x for each row values 1;
ERROR 42X62: 'CREATE TRIGGER' is not allowed in the 'SYS' schema.
ij> -- not on table in sys schema
-create trigger tr NO CASCADE before insert on sys.systables for each row mode db2sql values 1;
+create trigger tr NO CASCADE before insert on sys.systables for each row values 1;
ERROR X0Y56: 'CREATE TRIGGER' is not allowed on the System table 'SYS.SYSTABLES'.
ij> -- duplicate columns, not allowed
-create trigger tr NO CASCADE before update of x, x on x for each row mode db2sql values 1;
+create trigger tr NO CASCADE before update of x, x on x for each row values 1;
ERROR 42Y40: 'X' appears multiple times in the UPDATE OF column list for trigger 'TR'.
ij> -- no params in column list
-create trigger tr NO CASCADE before update of x, ? on x for each row mode db2sql values 1;
+create trigger tr NO CASCADE before update of x, ? on x for each row values 1;
ERROR 42X01: Syntax error: Encountered "?" at line 2, column 50.
ij> -- invalid column
-create trigger tr NO CASCADE before update of doesnotexist on x for each row mode db2sql values 1;
+create trigger tr NO CASCADE before update of doesnotexist on x for each row values 1;
ERROR 42X14: 'DOESNOTEXIST' is not a column in table or VTI 'X'.
ij> -- not on view
-create trigger tr NO CASCADE before insert on v for each row mode db2sql values 1;
+create trigger tr NO CASCADE before insert on v for each row values 1;
ERROR 42Y62: 'CREATE TRIGGER' is not allowed on 'APP.V' because it is a view.
ij> -- error to use table qualifier
-create trigger tr NO CASCADE before update of x.x on x for each row mode db2sql values 1;
+create trigger tr NO CASCADE before update of x.x on x for each row values 1;
ERROR 42X01: Syntax error: Encountered "." at line 2, column 48.
ij> -- error to use schema.table qualifier
-create trigger tr NO CASCADE before update of app.x.x on x for each row mode db2sql values 1;
+create trigger tr NO CASCADE before update of app.x.x on x for each row values 1;
ERROR 42X01: Syntax error: Encountered "." at line 2, column 50.
ij> -- no params in trigger action
-- bad
-create trigger tr NO CASCADE before delete on x for each row mode db2sql select * from x where x = ?;
+create trigger tr NO CASCADE before delete on x for each row select * from x where x = ?;
ERROR 42Y27: Parameters are not allowed in the trigger action.
-ij> create trigger stmttrigger NO CASCADE before delete on x for each statement mode db2sql values 1;
+ij> create trigger stmttrigger NO CASCADE before delete on x for each statement values 1;
0 rows inserted/updated/deleted
ij> select triggername, type from sys.systriggers where triggername = 'STMTTRIGGER';
TRIGGERNAME |&
@@ -95,7 +95,7 @@
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;
+ij> create trigger rowtrigger NO CASCADE before delete on x for each row values 1;
0 rows inserted/updated/deleted
ij> select triggername, type from sys.systriggers where triggername = 'ROWTRIGGER';
TRIGGERNAME |&
@@ -105,7 +105,7 @@
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;
+create trigger t2 NO CASCADE before update of x,y on x for each row values 1;
0 rows inserted/updated/deleted
ij> -- CREATE CONSTRAINT
alter table x add constraint ck2 check(x > 0);
@@ -131,7 +131,7 @@
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;
+ij> create trigger tins after insert on x referencing new_table as newtab for each statement insert into y select x, y, z from newtab;
0 rows inserted/updated/deleted
ij> insert into x values (1, 1, 1);
1 row inserted/updated/deleted
@@ -152,11 +152,11 @@
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;
+create trigger t2 NO CASCADE before update of x,y on x for each row values 1;
0 rows inserted/updated/deleted
-ij> create trigger t3 after update of x,y on x for each statement mode db2sql values 1;
+ij> create trigger t3 after update of x,y on x for each statement values 1;
0 rows inserted/updated/deleted
-ij> create trigger t4 after delete on x for each statement mode db2sql values 1;
+ij> create trigger t4 after delete on x for each statement values 1;
0 rows inserted/updated/deleted
ij> select cast(triggername as char(10)), tablename from sys.systriggers t, sys.systables tb
where t.tableid = tb.tableid order by 1;
@@ -180,11 +180,11 @@
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;
+ij> create trigger test.t1 NO CASCADE before delete on x for each row values 1;
0 rows inserted/updated/deleted
ij> set schema test;
0 rows inserted/updated/deleted
-ij> create trigger t2 NO CASCADE before delete on app.x for each row mode db2sql values 1;
+ij> create trigger t2 NO CASCADE before delete on app.x for each row values 1;
0 rows inserted/updated/deleted
ij> select schemaname, triggername from sys.systriggers t, sys.sysschemas s
where s.schemaid = t.schemaid;
@@ -216,19 +216,19 @@
ij> --
-- 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');
0 rows inserted/updated/deleted
-ij> create trigger t2 after insert on t for each statement mode db2sql
+ij> create trigger t2 after insert on t for each statement
values app.triggerFiresMin('1st');
0 rows inserted/updated/deleted
-ij> create trigger t3 no cascade before insert on t for each row mode db2sql
+ij> create trigger t3 no cascade before insert on t for each row
values app.triggerFiresMin('4th');
0 rows inserted/updated/deleted
-ij> create trigger t4 after insert on t for each row mode db2sql
+ij> create trigger t4 after insert on t for each row
values app.triggerFiresMin('2nd');
0 rows inserted/updated/deleted
-ij> create trigger t5 no cascade before insert on t for each statement mode db2sql
+ij> create trigger t5 no cascade before insert on t for each statement
values app.triggerFiresMin('5th');
0 rows inserted/updated/deleted
ij> insert into t values (1,1,'1');
@@ -250,19 +250,19 @@
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
+create trigger t1 after insert on t for each row
values app.triggerFires('3rd');
0 rows inserted/updated/deleted
-ij> create trigger t2 no cascade before insert on t for each statement mode db2sql
+ij> create trigger t2 no cascade before insert on t for each statement
values app.triggerFires('1st');
0 rows inserted/updated/deleted
-ij> create trigger t3 after insert on t for each row mode db2sql
+ij> create trigger t3 after insert on t for each row
values app.triggerFires('4th');
0 rows inserted/updated/deleted
-ij> create trigger t4 no cascade before insert on t for each row mode db2sql
+ij> create trigger t4 no cascade before insert on t for each row
values app.triggerFires('2nd');
0 rows inserted/updated/deleted
-ij> create trigger t5 after insert on t for each statement mode db2sql
+ij> create trigger t5 after insert on t for each statement
values app.triggerFires('5th');
0 rows inserted/updated/deleted
ij> insert into t values
@@ -401,10 +401,10 @@
-- 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');
0 rows inserted/updated/deleted
-ij> create trigger t2 after insert on t for each statement mode db2sql
+ij> create trigger t2 after insert on t for each statement
values app.triggerFires('STATEMENT: empty insert, ok');
0 rows inserted/updated/deleted
ij> insert into t select * from t;
@@ -419,10 +419,10 @@
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
+ij> create trigger t1 after update on t for each row
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
+ij> create trigger t2 after update on t for each statement
values app.triggerFires('STATEMENT: empty update, ok');
0 rows inserted/updated/deleted
ij> update t set x = x;
@@ -438,10 +438,10 @@
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
+ij> create trigger t1 after delete on t for each row
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
+ij> create trigger t2 after delete on t for each statement
values app.triggerFires('STATEMENT: empty delete, ok');
0 rows inserted/updated/deleted
ij> delete from t;
@@ -469,10 +469,10 @@
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
+ij> create trigger t1 no cascade before insert on f for each row
values app.triggerFiresMin('BEFORE constraints');
0 rows inserted/updated/deleted
-ij> create trigger t2 after insert on f for each row mode db2sql
+ij> create trigger t2 after insert on f for each row
values app.triggerFiresMin('AFTER constraints');
0 rows inserted/updated/deleted
ij> -- INSERT
@@ -506,10 +506,10 @@
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
+create trigger t1 no cascade before update on f for each row
values app.triggerFiresMin('BEFORE constraints');
0 rows inserted/updated/deleted
-ij> create trigger t2 after update on f for each row mode db2sql
+ij> create trigger t2 after update on f for each row
values app.triggerFiresMin('AFTER constraints');
0 rows inserted/updated/deleted
ij> -- fails, ck violated
@@ -542,10 +542,10 @@
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
+ij> create trigger t1 no cascade before delete on p for each row
values app.triggerFiresMin('BEFORE constraints');
0 rows inserted/updated/deleted
-ij> create trigger t2 after delete on p for each row mode db2sql
+ij> create trigger t2 after delete on p for each row
values app.triggerFiresMin('AFTER constraints');
0 rows inserted/updated/deleted
ij> -- fails, fk violated
@@ -577,22 +577,22 @@
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
+ij> create trigger tins after insert on t for each row
values app.triggerFiresMin('insert');
0 rows inserted/updated/deleted
-ij> create trigger tdel after delete on t for each row mode db2sql
+ij> create trigger tdel after delete on t for each row
values app.triggerFiresMin('delete');
0 rows inserted/updated/deleted
-ij> create trigger tupc1 after update of c1 on t for each row mode db2sql
+ij> create trigger tupc1 after update of c1 on t for each row
values app.triggerFiresMin('update c1');
0 rows inserted/updated/deleted
-ij> create trigger tupc2 after update of c2 on t for each row mode db2sql
+ij> create trigger tupc2 after update of c2 on t for each row
values app.triggerFiresMin('update c2');
0 rows inserted/updated/deleted
-ij> create trigger tupc1c2 after update of c1,c2 on t for each row mode db2sql
+ij> create trigger tupc1c2 after update of c1,c2 on t for each row
values app.triggerFiresMin('update c1,c2');
0 rows inserted/updated/deleted
-ij> create trigger tupc2c1 after update of c2,c1 on t for each row mode db2sql
+ij> create trigger tupc2c1 after update of c2,c1 on t for each row
values app.triggerFiresMin('update c2,c1');
0 rows inserted/updated/deleted
ij> insert into t values (1,1);
@@ -632,7 +632,7 @@
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
+referencing NEW as NEW for each row
insert into trighistory ("cOlUmN1", "cOlUmN2 ", "cOlUmN3"""" ") values (new."cOlUmN1" + 5, "NEW"."cOlUmN2 " * new."cOlUmN3"""" ", 5);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 2000;
@@ -650,7 +650,7 @@
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
+referencing new as new for each row
insert into trighistory ("cOlUmN1", "cOlUmN2 ", "cOlUmN3"""" ") values (new."cOlUmN1" + new."cOlUmN1", "NEW"."cOlUmN2 " * new."cOlUmN3"""" ", new."cOlUmN2 " * 3);
0 rows inserted/updated/deleted
ij> select cast(triggername as char(10)), CAST (TRIGGERDEFINITION AS VARCHAR(180)), STMTNAME from sys.systriggers t, sys.sysstatements s
@@ -677,7 +677,7 @@
0 rows inserted/updated/deleted
ij> 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);
0 rows inserted/updated/deleted
ij> insert into trigtable1 values (1, 1);
@@ -701,7 +701,7 @@
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;
+ij> create trigger tr after insert on t for each statement values 1;
0 rows inserted/updated/deleted
ij> prepare ps as 'insert into t values (?)';
ij> execute ps using 'values (1)';
@@ -739,7 +739,7 @@
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
+referencing OLD as OLD for each row
delete from parent where a = OLD.a;
0 rows inserted/updated/deleted
ij> insert into t1 values (0, 1);
@@ -785,7 +785,7 @@
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
+ old_table as old new_table as new for each statement insert into
removed select * from old where x not in (select x from
new where x < 10);
0 rows inserted/updated/deleted
@@ -812,7 +812,7 @@
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
+ old as oldrow new as newrow for each row insert into
removed values (newrow.x + oldrow.x);
0 rows inserted/updated/deleted
ij> update x set x=28 where x=18;
@@ -855,7 +855,7 @@
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;
+create trigger tgood after insert on x for each statement insert into x values 666;
0 rows inserted/updated/deleted
ij> insert into x values 1;
ERROR 54038: Maximum depth of nested triggers was exceeded.
@@ -864,7 +864,7 @@
-----------
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;
+ij> create trigger tgood after insert on x for each statement delete from x;
0 rows inserted/updated/deleted
ij> insert into x values 1;
1 row inserted/updated/deleted
@@ -873,7 +873,7 @@
-----------
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;
+ij> create trigger tgood after insert on x for each statement update x set x = x+100;
0 rows inserted/updated/deleted
ij> insert into x values 1;
1 row inserted/updated/deleted
@@ -886,7 +886,7 @@
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);
+for each statement insert into x values (666), (999), (333);
0 rows inserted/updated/deleted
ij> insert into x values 1;
ERROR 54038: Maximum depth of nested triggers was exceeded.
@@ -899,7 +899,7 @@
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);
+for each row insert into x values (n.x);
0 rows inserted/updated/deleted
ij> insert into x values 7;
ERROR 54038: Maximum depth of nested triggers was exceeded.
@@ -912,7 +912,7 @@
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);
+for each row insert into x values (333), (999), (333);
0 rows inserted/updated/deleted
ij> insert into x values 1;
ERROR 54038: Maximum depth of nested triggers was exceeded.
@@ -926,7 +926,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;
@@ -953,7 +953,7 @@
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;
+ij(CONNECTION1)> create trigger mytrigger after update on myschema.mytable for each row select * from sys.systables;
0 rows inserted/updated/deleted
ij(CONNECTION1)> rollback;
ij(CONNECTION1)> disconnect;
@@ -966,7 +966,7 @@
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
+for each row
insert into t438_t(id, bl, l, nc, oc) values (n.id, n.bl, length(n.bl), n.cost, o.cost);
0 rows inserted/updated/deleted
ij> -- initially just some small BLOB values.
@@ -1013,7 +1013,7 @@
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
+for each row
insert into t438_t(id, cl, l, nc, oc) values (n.id, n.cl, length(n.cl), n.cost, o.cost);
0 rows inserted/updated/deleted
ij> -- initially just some small CLOB values.
@@ -1060,7 +1060,7 @@
0 rows inserted/updated/deleted
ij> 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");
0 rows inserted/updated/deleted
ij> insert into tsn values (1, 234);
@@ -1092,13 +1092,13 @@
ij> 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);
0 rows inserted/updated/deleted
ij> 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);
0 rows inserted/updated/deleted
ij> INSERT INTO T10641 VALUES (0);
@@ -1145,7 +1145,7 @@
ij> 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;
0 rows inserted/updated/deleted
ij> insert into test(info) values (1),(2),(3);
@@ -1160,7 +1160,7 @@
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;
+ij> create trigger tt after insert on t1 for each statement insert into t2 values 1;
0 rows inserted/updated/deleted
ij> insert into t1 values 1;
1 row inserted/updated/deleted
@@ -1198,7 +1198,7 @@
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;
+ij> create trigger tt2 after insert on t3 for each statement insert into t4 values 1;
0 rows inserted/updated/deleted
ij> insert into t3 values 1;
1 row inserted/updated/deleted
@@ -1223,7 +1223,7 @@
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);
+ij> create trigger test_trigger AFTER insert on test FOR EACH ROW values max_value(2,4);
0 rows inserted/updated/deleted
ij> insert into test values(1);
1 row inserted/updated/deleted
@@ -1249,7 +1249,7 @@
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
+ij> create trigger tr31t31TriggerTest after insert on t31TriggerTest for each statement
insert into t32TriggerTest values (select c111 from v21ViewTest where c112=1);
0 rows inserted/updated/deleted
ij> -- try an insert which will fire the trigger
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerRecursion.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerRecursion.out?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerRecursion.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerRecursion.out Mon Oct 30 01:34:06 2006
@@ -52,39 +52,39 @@
0 rows inserted/updated/deleted
ij> create table t17 (x int);
0 rows inserted/updated/deleted
-ij> create trigger tr1 after insert on t1 for each row MODE DB2SQL insert into t2 values 666;
+ij> create trigger tr1 after insert on t1 for each row insert into t2 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr2 after insert on t2 for each row MODE DB2SQL insert into t3 values 666;
+ij> create trigger tr2 after insert on t2 for each row insert into t3 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr3 after insert on t3 for each row MODE DB2SQL insert into t4 values 666;
+ij> create trigger tr3 after insert on t3 for each row insert into t4 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr4 after insert on t4 for each row MODE DB2SQL insert into t5 values 666;
+ij> create trigger tr4 after insert on t4 for each row insert into t5 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr5 after insert on t5 for each row MODE DB2SQL insert into t6 values 666;
+ij> create trigger tr5 after insert on t5 for each row insert into t6 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr6 after insert on t6 for each row MODE DB2SQL insert into t7 values 666;
+ij> create trigger tr6 after insert on t6 for each row insert into t7 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr7 after insert on t7 for each row MODE DB2SQL insert into t8 values 666;
+ij> create trigger tr7 after insert on t7 for each row insert into t8 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr8 after insert on t8 for each row MODE DB2SQL insert into t9 values 666;
+ij> create trigger tr8 after insert on t8 for each row insert into t9 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr9 after insert on t9 for each row MODE DB2SQL insert into t10 values 666;
+ij> create trigger tr9 after insert on t9 for each row insert into t10 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr10 after insert on t10 for each row MODE DB2SQL insert into t11 values 666;
+ij> create trigger tr10 after insert on t10 for each row insert into t11 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr11 after insert on t11 for each row MODE DB2SQL insert into t12 values 666;
+ij> create trigger tr11 after insert on t11 for each row insert into t12 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr12 after insert on t12 for each row MODE DB2SQL insert into t13 values 666;
+ij> create trigger tr12 after insert on t12 for each row insert into t13 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr13 after insert on t13 for each row MODE DB2SQL insert into t14 values 666;
+ij> create trigger tr13 after insert on t13 for each row insert into t14 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr14 after insert on t14 for each row MODE DB2SQL insert into t15 values 666;
+ij> create trigger tr14 after insert on t14 for each row insert into t15 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr15 after insert on t15 for each row MODE DB2SQL insert into t16 values 666;
+ij> create trigger tr15 after insert on t15 for each row insert into t16 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr16 after insert on t16 for each row MODE DB2SQL insert into t17 values 666;
+ij> create trigger tr16 after insert on t16 for each row insert into t17 values 666;
0 rows inserted/updated/deleted
-ij> create trigger tr17 after insert on t17 for each row MODE DB2SQL values 1;
+ij> create trigger tr17 after insert on t17 for each row values 1;
0 rows inserted/updated/deleted
ij> -- here we go
;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerRefClause.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerRefClause.out?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerRefClause.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerRefClause.out Mon Oct 30 01:34:06 2006
@@ -28,39 +28,39 @@
--
-- 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 as oldrow for each row values 1;
ERROR 42Y92: INSERT triggers may only reference new transition variables/tables.
-ij> create trigger t1 after insert on x referencing old_table as oldtab for each statement mode db2sql values 1;
+ij> create trigger t1 after insert on x referencing old_table as oldtab for each statement values 1;
ERROR 42Y92: INSERT triggers may only reference new transition variables/tables.
-ij> create trigger t1 after insert on x referencing old_table as oldtab for each statement mode db2sql values 1;
+ij> create trigger t1 after insert on x referencing old_table as oldtab for each statement values 1;
ERROR 42Y92: INSERT triggers may only reference new transition variables/tables.
-ij> create trigger t1 after delete on x referencing new as newrow for each row mode db2sql values 1;
+ij> create trigger t1 after delete on x referencing new as newrow for each row values 1;
ERROR 42Y92: DELETE triggers may only reference old transition variables/tables.
-ij> create trigger t1 after delete on x referencing new_table as newtab for each statement mode db2sql values 1;
+ij> create trigger t1 after delete on x referencing new_table as newtab for each statement values 1;
ERROR 42Y92: DELETE triggers may only reference old transition variables/tables.
-ij> create trigger t1 after delete on x referencing new_table as newtab for each statement mode db2sql values 1;
+ij> create trigger t1 after delete on x referencing new_table as newtab for each statement values 1;
ERROR 42Y92: DELETE triggers may only reference old transition variables/tables.
ij> -- 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 as old for each row values old.x;
ERROR 42Y92: INSERT triggers may only reference new transition variables/tables.
-ij> create trigger t1 after insert on x referencing old_table as old for each statement mode db2sql select * from old;
+ij> create trigger t1 after insert on x referencing old_table as old for each statement select * from old;
ERROR 42Y92: INSERT triggers may only reference new transition variables/tables.
-ij> create trigger t1 after insert on x referencing old_table as old for each statement mode db2sql select * from old;
+ij> create trigger t1 after insert on x referencing old_table as old for each statement select * from old;
ERROR 42Y92: INSERT triggers may only reference new transition variables/tables.
-ij> create trigger t1 after delete on x referencing new as new for each row mode db2sql values new.x;
+ij> create trigger t1 after delete on x referencing new as new for each row values new.x;
ERROR 42Y92: DELETE triggers may only reference old transition variables/tables.
-ij> create trigger t1 after delete on x referencing new_table as new for each statement mode db2sql select * from new;
+ij> create trigger t1 after delete on x referencing new_table as new for each statement select * from new;
ERROR 42Y92: DELETE triggers may only reference old transition variables/tables.
-ij> create trigger t1 after delete on x referencing new_table as new for each statement mode db2sql select * from new;
+ij> create trigger t1 after delete on x referencing new_table as new for each statement select * from new;
ERROR 42Y92: DELETE triggers may only reference old transition variables/tables.
ij> -- 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 values old.badcol;
ERROR 42X04: Column 'OLD.BADCOL' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'OLD.BADCOL' is not a column in the target table.
-ij> create trigger t1 after delete on x referencing old as old for each row mode db2sql values old;
+ij> create trigger t1 after delete on x referencing old as old for each row values old;
ERROR 42X04: Column 'OLD' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'OLD' is not a column in the target table.
-ij> create trigger t1 after delete on x referencing old as oldrow for each row mode db2sql values oldrow.badcol;
+ij> create trigger t1 after delete on x referencing old as oldrow for each row values oldrow.badcol;
ERROR 42X04: Column 'OLDROW.BADCOL' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'OLDROW.BADCOL' is not a column in the target table.
-ij> create trigger t1 after delete on x referencing old as oldrow for each row mode db2sql values oldrow;
+ij> create trigger t1 after delete on x referencing old as oldrow for each row values oldrow;
ERROR 42X04: Column 'OLDROW' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'OLDROW' is not a column in the target table.
ij> -- lets try some basics with old/new table
create table y (x int);
@@ -69,7 +69,7 @@
6 rows inserted/updated/deleted
ij> insert into x values (1, null, null), (2, null, null);
2 rows inserted/updated/deleted
-ij> create trigger t1 after delete on x referencing old as old for each row mode db2sql delete from y where x = old.x;
+ij> create trigger t1 after delete on x referencing old as old for each row delete from y where x = old.x;
0 rows inserted/updated/deleted
ij> autocommit off;
ij> delete from x;
@@ -82,7 +82,7 @@
ij> drop trigger t1;
0 rows inserted/updated/deleted
ij> commit;
-ij> 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);
+ij> 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);
0 rows inserted/updated/deleted
ij> delete from x;
2 rows inserted/updated/deleted
@@ -124,7 +124,7 @@
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));
0 rows inserted/updated/deleted
-ij> create trigger t1 after insert on allTypes1 referencing new as newrowtab for each row mode db2sql
+ij> 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,
@@ -180,7 +180,7 @@
ij> create table removed (x int);
0 rows inserted/updated/deleted
ij> -- 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;
9 rows inserted/updated/deleted
@@ -210,7 +210,7 @@
0 rows inserted/updated/deleted
ij> create table y (x int, y int);
0 rows inserted/updated/deleted
-ij> create trigger t1 after insert on x referencing new_table as newtab for each statement mode db2sql
+ij> 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;
0 rows inserted/updated/deleted
ij> insert into x values (1,1);
@@ -224,7 +224,7 @@
ij> drop trigger t1;
0 rows inserted/updated/deleted
ij> -- 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;
0 rows inserted/updated/deleted
ij> insert into x values (1,1);
@@ -247,7 +247,7 @@
0 rows inserted/updated/deleted
ij> create table y (b char(5) FOR BIT DATA);
0 rows inserted/updated/deleted
-ij> create trigger t1 after insert on x referencing new as new for each row mode db2sql insert into y values (new.b || X'80');
+ij> create trigger t1 after insert on x referencing new as new for each row insert into y values (new.b || X'80');
0 rows inserted/updated/deleted
ij> insert into x values (X'E0');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA 'e02020202080' to length 5.
@@ -256,7 +256,7 @@
----------
ij> drop trigger t1;
0 rows inserted/updated/deleted
-ij> create trigger t1 after insert on x referencing new as new for each row mode db2sql insert into y values new.b;
+ij> create trigger t1 after insert on x referencing new as new for each row insert into y values new.b;
0 rows inserted/updated/deleted
ij> insert into x values null;
1 row inserted/updated/deleted
@@ -266,7 +266,7 @@
NULL
ij> drop trigger t1;
0 rows inserted/updated/deleted
-ij> create trigger t1 after insert on x referencing new as new for each row mode db2sql insert into val values length(new.b);
+ij> create trigger t1 after insert on x referencing new as new for each row insert into val values length(new.b);
0 rows inserted/updated/deleted
ij> insert into x values X'FFE0';
1 row inserted/updated/deleted
@@ -300,7 +300,7 @@
ij> create table s_t1(col1 int not null primary key, chgType char(20));
0 rows inserted/updated/deleted
ij> -- 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));
@@ -308,7 +308,7 @@
ij> drop trigger trig_delete_2;
0 rows inserted/updated/deleted
ij> -- 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/master/xml_general.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/xml_general.out?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/xml_general.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/xml_general.out Mon Oct 30 01:34:06 2006
@@ -177,9 +177,9 @@
not logged on commit preserve rows;
ERROR 42962: Long column type column or parameter 'MYX' not permitted in declared global temporary tables or procedure definitions.
ij> -- 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 tr2 after insert on t1 for each row update t1 set x = 'hmm';
ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'.
-ij> create trigger tr1 after insert on t1 for each row mode db2sql update t1 set x = null;
+ij> create trigger tr1 after insert on t1 for each row update t1 set x = null;
0 rows inserted/updated/deleted
ij> drop trigger tr1;
0 rows inserted/updated/deleted
@@ -1251,7 +1251,7 @@
0 rows inserted/updated/deleted
ij> insert into t9 values (1, xmlparse(document '<name> john </name>' preserve whitespace));
1 row inserted/updated/deleted
-ij> create trigger tx after insert on t9 for each statement mode db2sql
+ij> create trigger tx after insert on t9 for each statement
insert into t10 values (1, xmlparse(document '<name> jane </name>' preserve whitespace));
0 rows inserted/updated/deleted
ij> insert into t9 values (2, xmlparse(document '<name> ally </name>' preserve whitespace));
@@ -1287,7 +1287,7 @@
2 rows inserted/updated/deleted
ij> insert into t9 values (1, xmlparse(document '<name> john </name>' preserve whitespace));
1 row inserted/updated/deleted
-ij> create trigger tx after insert on t9 for each statement mode db2sql
+ij> 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));
0 rows inserted/updated/deleted
ij> insert into t9 values (2, xmlparse(document '<name> ally </name>' preserve whitespace));
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/prepStmtMetaData.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/prepStmtMetaData.java?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/prepStmtMetaData.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/prepStmtMetaData.java Mon Oct 30 01:34:06 2006
@@ -81,7 +81,7 @@
testMetaData(con, "drop schema myschema restrict", true);
// test create trigger - meta data should be null
- //testMetaData(con, "create trigger mytrig after insert on ab for each row mode db2sql create table yy(a int)", true);
+ //testMetaData(con, "create trigger mytrig after insert on ab for each row create table yy(a int)", true);
// test drop trigger - meta data should be null
//testMetaData(con, "drop trigger mytrig", true);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql Mon Oct 30 01:34:06 2006
@@ -169,8 +169,8 @@
create table tab4 (c1 int, c2 int, c3 int, c4 int);
create table tab5 (c1 int);
insert into tab4 values (1,2,3,4);
-create trigger tr1 after update of c2, c3, c4 on tab4 for each row mode db2sql insert into tab5 values (1);
-create trigger tr2 after update of c3, c4 on tab4 for each row mode db2sql insert into tab5 values (2);
+create trigger tr1 after update of c2, c3, c4 on tab4 for each row insert into tab5 values (1);
+create trigger tr2 after update of c3, c4 on tab4 for each row insert into tab5 values (2);
-- tr1 is dropped, tr2 still OK
drop trigger tr1;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql Mon Oct 30 01:34:06 2006
@@ -170,7 +170,7 @@
-- drop column restrict should fail because column is used in a trigger:
create table atdc_6 (a integer, b integer);
create trigger atdc_6_trigger_1 after update of b on atdc_6
- for each row mode db2sql values current_date;
+ for each row values current_date;
alter table atdc_6 drop column b restrict;
select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
alter table atdc_6 drop column b cascade;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql Mon Oct 30 01:34:06 2006
@@ -220,7 +220,7 @@
create table t1 (c1 int generated always as identity, name char(32));
create table t2 (c2 int generated always as identity, name char(32));
-create trigger insert_trigger after insert on t1 for each row mode db2sql
+create trigger insert_trigger after insert on t1 for each row
insert into t2 (name) values ('Bob Finocchio');
insert into t1 (name) values ('Phil White');
@@ -302,7 +302,7 @@
lvl int);
create table tab3 (c1 int);
-create trigger tab1_after1 after insert on tab3 referencing new as newrow for each row mode db2sql insert into tab1 (lvl) values 1,2,3;
+create trigger tab1_after1 after insert on tab3 referencing new as newrow for each row insert into tab1 (lvl) values 1,2,3;
insert into tab3 values null;
select * from tab1;
@@ -310,7 +310,7 @@
create table tab2 (lvl int, s1 bigint generated always as identity);
-create trigger tab1_after2 after insert on tab3 referencing new as newrow for each row mode db2sql insert into tab2 (lvl) values 1,2,3;
+create trigger tab1_after2 after insert on tab3 referencing new as newrow for each row insert into tab2 (lvl) values 1,2,3;
insert into tab3 values null;
select * from tab2;
@@ -883,7 +883,7 @@
-- statement table and not for the table that got modified by the trigger
create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int);
create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int);
-create trigger t1tr1 after insert on t1 for each row mode db2sql insert into t2 (c22) values (1);
+create trigger t1tr1 after insert on t1 for each row insert into t2 (c22) values (1);
values IDENTITY_VAL_LOCAL();
insert into t1 (c12) values (1);
-- IDENTITY_VAL_LOCAL will return 101 which got generated for table t1.
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/dropTable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/dropTable.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/dropTable.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/dropTable.sql Mon Oct 30 01:34:06 2006
@@ -179,21 +179,21 @@
--
create table t1(a int);
create table t2(a int);
-create trigger t1trig after insert on t1 for each row mode db2sql insert into t2 values(1);
+create trigger t1trig after insert on t1 for each row insert into t2 values(1);
-- this should work - trigger should be deleted
drop table t1;
-- t1 shouldn't be found
select * from t1;
create table t1(a int);
-create trigger t1trig after insert on t1 for each row mode db2sql insert into t2 values(1);
+create trigger t1trig after insert on t1 for each row insert into t2 values(1);
-- this should work - trigger should be deleted
drop table t1;
-- t1 shouldn't be found
select * from t1;
create table t1(a int);
-create trigger t1trig after insert on t1 for each row mode db2sql insert into t2 values(1);
+create trigger t1trig after insert on t1 for each row insert into t2 values(1);
-- this should work - trigger should be deleted
drop table t1;
-- t1 shouldn't be found
@@ -205,7 +205,7 @@
--
create table t1(a int);
create table t2(a int);
-create trigger t2trig after insert on t2 for each row mode db2sql insert into t1 values(1);
+create trigger t2trig after insert on t2 for each row insert into t1 values(1);
-- this should work
drop table t1;
-- the following should get an error when trying to recompile the trigger action
@@ -214,7 +214,7 @@
create table t1(a int);
create table t2(a int);
-create trigger t2trig after insert on t2 for each row mode db2sql insert into t1 values(1);
+create trigger t2trig after insert on t2 for each row insert into t1 values(1);
-- this should work
drop table t1;
-- the following should get an error when trying to recompile the trigger action
@@ -223,7 +223,7 @@
create table t1(a int);
create table t2(a int);
-create trigger t2trig after insert on t2 for each row mode db2sql insert into t1 values(1);
+create trigger t2trig after insert on t2 for each row insert into t1 values(1);
-- this should work
drop table t1;
-- the following should get an error when trying to recompile the trigger action
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java Mon Oct 30 01:34:06 2006
@@ -1807,7 +1807,7 @@
sb.append("\".\"");
sb.append(table);
sb.append("\"");
- sb.append(" for each row mode db2sql values 1");
+ sb.append(" for each row values 1");
boolean savedAutoCommit = user.getConnection().getAutoCommit();
String currentSchema = getUserCurrentSchema(user);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql Mon Oct 30 01:34:06 2006
@@ -188,8 +188,8 @@
-- Some TRIGGER privilege checks. See GrantRevoke.java for more tests
set connection swiperConnection;
-- Should fail
-create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
-create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+create trigger trig_sat1 after update on satheesh.tsat for each statement values 1;
+create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement values 1;
-- Grant trigger privilege
set connection satConnection;
@@ -197,8 +197,8 @@
-- Try now
set connection swiperConnection;
-create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
-create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+create trigger trig_sat1 after update on satheesh.tsat for each statement values 1;
+create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement values 1;
drop trigger trig_sat1;
drop trigger trig_sat2;
@@ -208,16 +208,16 @@
revoke trigger on tsat from swiper;
set connection swiperConnection;
-create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
-create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+create trigger trig_sat1 after update on satheesh.tsat for each statement values 1;
+create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement values 1;
-- Now grant access to public and try again
set connection satConnection;
grant trigger on tsat to public;
set connection swiperConnection;
-create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
-create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+create trigger trig_sat1 after update on satheesh.tsat for each statement values 1;
+create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement values 1;
drop trigger trig_sat1;
drop trigger trig_sat2;
@@ -811,7 +811,7 @@
drop table t32TriggerTest;
create table t32TriggerTest (c321 int);
-- following should fail because not all the privileges are in place
-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 mamta2.v21ViewTest where c112=1);
insert into t31TriggerTest values(1);
select * from t31TriggerTest;
@@ -1269,7 +1269,7 @@
drop table t22TriggerTest;
create table t22TriggerTest (c221 int);
-- following should pass because all the privileges are in places
-create trigger tr21t21TriggerTest after insert on t21TriggerTest for each statement mode db2sql
+create trigger tr21t21TriggerTest after insert on t21TriggerTest for each statement
insert into t22TriggerTest values (select c111 from mamta1.t11TriggerTest where c112=1);
insert into t21TriggerTest values(1);
select * from t21TriggerTest;
@@ -1293,7 +1293,7 @@
set connection mamta2;
create table t21TriggerTest (c211 int);
-- following should pass because all the privileges are in places
-create trigger tr21t21TriggerTest after insert on t21TriggerTest for each statement mode db2sql
+create trigger tr21t21TriggerTest after insert on t21TriggerTest for each statement
insert into mamta1.t12RoutineTest values (select c111 from mamta1.t11TriggerTest where c111=1);
-- this insert's trigger will cause a new row in mamta1.t12RoutineTest
insert into t21TriggerTest values(1);
@@ -1303,13 +1303,13 @@
select * from t12RoutineTest;
set connection mamta2;
-- following should fail because mamta2 doesn't have trigger permission on mamta1.t11TriggerTest
-create trigger tr11t11TriggerTest after insert on mamta1.t11TriggerTest for each statement mode db2sql
+create trigger tr11t11TriggerTest after insert on mamta1.t11TriggerTest for each statement
insert into mamta1.t12RoutineTest values (1);
set connection mamta1;
grant trigger on t11TriggerTest to mamta2;
set connection mamta2;
-- following will pass now because mamta2 has trigger permission on mamta1.t11TriggerTest
-create trigger tr11t11TriggerTest after insert on mamta1.t11TriggerTest for each statement mode db2sql
+create trigger tr11t11TriggerTest after insert on mamta1.t11TriggerTest for each statement
insert into mamta1.t12RoutineTest values (1);
-- following will fail becuae mamta2 has TRIGGER privilege but not INSERT privilege on mamta1.t11TriggerTest
insert into mamta1.t11TriggerTest values(3);
@@ -1345,7 +1345,7 @@
drop table t31TriggerTest;
create table t31TriggerTest(c11 int);
-- following will pass because all the required privileges are in place for mamta3
-create trigger tr31t31 after insert on t31TriggerTest for each statement mode db2sql
+create trigger tr31t31 after insert on t31TriggerTest for each statement
insert into mamta1.t13TriggerTest values (values mamta1.selectFromSpecificSchema(1));
-- following insert will cause a row to be inserted into mamta1.t13TriggerTest if the session user
-- has SELECT privilege on mamta1.t12RoutineTest. This shows that although triggers execute
@@ -1461,7 +1461,7 @@
drop table t41TriggerTest;
create table t41TriggerTest (c411 int);
drop trigger tr41t41;
-create trigger tr41t41 after insert on t41TriggerTest for each statement mode db2sql
+create trigger tr41t41 after insert on t41TriggerTest for each statement
insert into mamta3.t31TriggerTest (select * from mamta2.v21ViewTest);
insert into t41TriggerTest values(1);
insert into t41TriggerTest values(2);
@@ -1548,7 +1548,7 @@
drop table t21TriggerRevokeTest;
create table t21TriggerRevokeTest (c211 int);
-- following will pass because mamta2 has trigger permission on mamta1.t11TriggerRevokeTest
-create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode db2sql
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement
insert into t21TriggerRevokeTest values(99);
-- no data in the table in which trigger is going to insert
select * from t21TriggerRevokeTest;
@@ -1567,13 +1567,13 @@
-- no more rows inserted since last check
select * from t21TriggerRevokeTest;
-- following attempt to create insert trigger again will fail because trigger privilege has been revoked.
-create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode db2sql
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement
insert into t21TriggerRevokeTest values(99);
set connection mamta1;
grant trigger on t11TriggerRevokeTest to mamta2;
set connection mamta2;
-- following attempt to create insert trigger again will pass because mamta2 has got the necessary trigger privilege.
-create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode db2sql
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement
insert into t21TriggerRevokeTest values(99);
select * from t21TriggerRevokeTest;
set connection mamta1;
@@ -1607,7 +1607,7 @@
drop table t21TriggerRevokeTest;
create table t21TriggerRevokeTest (c211 int);
-- following will pass because mamta2 has trigger permission on mamta1.t11TriggerRevokeTest
-create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode db2sql
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement
insert into t21TriggerRevokeTest values(99);
-- no data in the table in which trigger is going to insert
select * from t21TriggerRevokeTest;
@@ -1628,7 +1628,7 @@
-- no more rows inserted since last check
select * from t21TriggerRevokeTest;
-- following attempt to create insert trigger again will pas because TRIGGER privilege was never revoked.
-create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode db2sql
+create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement
insert into t21TriggerRevokeTest values(99);
set connection mamta1;
-- insert trigger should get fired
@@ -1662,10 +1662,10 @@
create table t21TriggerRevokeTest (c211 int);
insert into t21TriggerRevokeTest values(1);
-- following will pass because mamta2 has required permissions on mamta1.t11TriggerRevokeTest
-create trigger tr211t21 after insert on t21TriggerRevokeTest for each statement mode db2sql
+create trigger tr211t21 after insert on t21TriggerRevokeTest for each statement
insert into mamta1.t11TriggerRevokeTest values(99);
-- following will pass because mamta2 has required permissions on mamta1.t11TriggerRevokeTest
-create trigger tr212t21 after insert on t21TriggerRevokeTest for each statement mode db2sql
+create trigger tr212t21 after insert on t21TriggerRevokeTest for each statement
insert into mamta1.t12TriggerRevokeTest values(99);
insert into t21TriggerRevokeTest values(1);
set connection mamta1;
@@ -1904,7 +1904,7 @@
grant UPDATE(c122, c121) on t12TriggerRevokeTest to mamta2;
set connection mamta2;
create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest
-for each statement mode db2sql
+for each statement
update mamta1.t12TriggerRevokeTest set c122 = 99;
set connection mamta1;
select * from t11TriggerRevokeTest;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2.sql Mon Oct 30 01:34:06 2006
@@ -101,7 +101,7 @@
-- tests
create trigger grant_select_trig AFTER delete on t1
- for each STATEMENT mode db2sql call grant_select_proc1();
+ for each STATEMENT call grant_select_proc1();
-- should fail
delete from t1 where i = 1;
-- check delete failed
@@ -113,7 +113,7 @@
set connection user1;
create trigger grant_select_trig AFTER delete on t1
- for each STATEMENT mode db2sql call grant_select_proc2();
+ for each STATEMENT call grant_select_proc2();
-- should fail
delete from t1 where i = 2;
-- check delete failed
@@ -125,7 +125,7 @@
set connection user1;
create trigger grant_select_trig AFTER delete on t1
- for each STATEMENT mode db2sql call grant_select_proc3();
+ for each STATEMENT call grant_select_proc3();
-- should fail
delete from t1 where i = 3;
-- check delete failed
@@ -137,7 +137,7 @@
set connection user1;
create trigger grant_select_trig AFTER delete on t1
- for each STATEMENT mode db2sql call grant_select_proc4();
+ for each STATEMENT call grant_select_proc4();
-- ok
delete from t1 where i = 4;
-- check delete
@@ -149,7 +149,7 @@
set connection user1;
create trigger revoke_select_trig AFTER delete on t1
- for each STATEMENT mode db2sql call revoke_select_proc1();
+ for each STATEMENT call revoke_select_proc1();
-- should fail
delete from t1 where i = 5;
-- check delete failed
@@ -161,7 +161,7 @@
set connection user1;
create trigger revoke_select_trig AFTER delete on t1
- for each STATEMENT mode db2sql call revoke_select_proc2();
+ for each STATEMENT call revoke_select_proc2();
-- should fail
delete from t1 where i = 6;
-- check delete failed
@@ -173,7 +173,7 @@
set connection user1;
create trigger revoke_select_trig AFTER delete on t1
- for each STATEMENT mode db2sql call revoke_select_proc3();
+ for each STATEMENT call revoke_select_proc3();
-- should fail
delete from t1 where i = 7;
-- check delete failed
@@ -185,7 +185,7 @@
set connection user1;
create trigger revoke_select_trig AFTER delete on t1
- for each STATEMENT mode db2sql call revoke_select_proc4();
+ for each STATEMENT call revoke_select_proc4();
-- ok
delete from t1 where i = 8;
-- check delete
@@ -245,7 +245,7 @@
-- test REFERENCES privilege, expect error
create table t2 (c1 int primary key not null, c2 int references user1.t1);
-- test TRIGGER privilege, expect error
-create trigger trigger1 after update on user1.t1 for each statement mode db2sql values integer('123');
+create trigger trigger1 after update on user1.t1 for each statement values integer('123');
-- try to DROP user1.idx1 index, expect error
drop index user1.idx1;
-- try to DROP user1.t1 table, expect error
@@ -1104,9 +1104,9 @@
set connection user1;
-- expect error
-create trigger tt0a after insert on t1 for each statement mode db2sql grant select on t1 to user2;
+create trigger tt0a after insert on t1 for each statement grant select on t1 to user2;
-- expect error
-create trigger tt0b after insert on t1 for each statement mode db2sql revoke select on t1 from user2;
+create trigger tt0b after insert on t1 for each statement revoke select on t1 from user2;
-- same schema in trigger action
drop table t6;
@@ -1116,8 +1116,8 @@
drop table t7;
create table t7 (c1 int, c2 int, c3 int);
insert into t7 values (1,1,1);
-create trigger tt1 after insert on user1.t6 for each statement mode db2sql update user2.t7 set c2 = 888;
-create trigger tt2 after insert on user1.t6 for each statement mode db2sql insert into user2.t7 values (2,2,2);
+create trigger tt1 after insert on user1.t6 for each statement update user2.t7 set c2 = 888;
+create trigger tt2 after insert on user1.t6 for each statement insert into user2.t7 values (2,2,2);
set connection user1;
insert into t6 values (1, 10);
@@ -1135,8 +1135,8 @@
grant trigger on t8 to user2;
grant update(c2, c1), insert on t9 to user2;
set connection user2;
-create trigger tt3 after insert on user1.t8 for each statement mode db2sql update user1.t9 set c2 = 888;
-create trigger tt4 after insert on user1.t8 for each statement mode db2sql insert into user1.t9 values (2,2,2);
+create trigger tt3 after insert on user1.t8 for each statement update user1.t9 set c2 = 888;
+create trigger tt4 after insert on user1.t8 for each statement insert into user1.t9 values (2,2,2);
set connection user1;
-- expect error
insert into user1.t8 values (1, 10);
@@ -1154,9 +1154,9 @@
grant all privileges on t11 to user2;
set connection user2;
-- ok
-create trigger tt5 after update on user1.t10 for each statement mode db2sql insert into user1.t11 values 1;
-create trigger tt6 after update of i on user1.t10 for each statement mode db2sql insert into user1.t11 values 2;
-create trigger tt7 after update of j on user1.t10 for each statement mode db2sql insert into user1.t11 values 3;
+create trigger tt5 after update on user1.t10 for each statement insert into user1.t11 values 1;
+create trigger tt6 after update of i on user1.t10 for each statement insert into user1.t11 values 2;
+create trigger tt7 after update of j on user1.t10 for each statement insert into user1.t11 values 3;
update user1.t10 set i=10;
select * from user1.t10;
@@ -1175,7 +1175,7 @@
grant trigger on t10 to user2;
set connection user2;
-create trigger tt8 after update of j on user1.t10 for each statement mode db2sql delete from user1.t11;
+create trigger tt8 after update of j on user1.t10 for each statement delete from user1.t11;
update user1.t10 set j=100;
select * from user1.t10;
@@ -1200,8 +1200,8 @@
grant select on v to user5;
set connection user5;
-create trigger tt9 after insert on user1.t10 for each statement mode db2sql insert into user1.t11 values (user1.F_ABS1(-5));
-create trigger tt10 after insert on user1.t10 for each statement mode db2sql insert into user1.t11 select * from user1.v;
+create trigger tt9 after insert on user1.t10 for each statement insert into user1.t11 values (user1.F_ABS1(-5));
+create trigger tt10 after insert on user1.t10 for each statement insert into user1.t11 select * from user1.v;
insert into user1.t10 values (1,1);
select * from user1.t10;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/identifier.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/identifier.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/identifier.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/identifier.sql Mon Oct 30 01:34:06 2006
@@ -96,8 +96,8 @@
create view vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvx as select * from idtest1;
create view "vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvy" as select * from idtest1;
-- trigger
-create trigger ttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttx after insert on idtest1 for each row mode db2sql update idtest2 set i=i;
-create trigger "ttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttx" after insert on idtest1 for each row mode db2sql update idtest2 set i=i;
+create trigger ttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttx after insert on idtest1 for each row update idtest2 set i=i;
+create trigger "ttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttx" after insert on idtest1 for each row update idtest2 set i=i;
-- schema
create schema ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssx;
create schema "ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssx";
@@ -140,8 +140,8 @@
create view vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv as select * from idtest1;
create view "vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvw" as select * from idtest1;
-- trigger
-create trigger tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt after insert on idtest1 for each row mode db2sql update idtest2 set i=i;
-create trigger "tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt" after insert on idtest1 for each row mode db2sql update idtest2 set i=i;
+create trigger tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt after insert on idtest1 for each row update idtest2 set i=i;
+create trigger "tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt" after insert on idtest1 for each row update idtest2 set i=i;
-- schema
create schema ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss;
create schema "ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss";
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql Mon Oct 30 01:34:06 2006
@@ -177,12 +177,12 @@
create trigger xr.be_uname_i after insert on xr.businessentity
referencing new as n
-for each row mode db2sql
+for each row
update xr.businessentity set uname = upper( n.name ) where name = n.name;
create trigger xr.be_unane_u after update of name, uname on xr.businessentity
referencing new as n
-for each row mode db2sql
+for each row
update xr.businessentity set uname = upper( n.name )
where name = n.name and uname <> upper( n.name );
@@ -312,12 +312,12 @@
create trigger xr.rov_uname_i after insert on xr.repositoryobjectversion
referencing new as n
-for each row mode db2sql
+for each row
update xr.repositoryobjectversion set uname = upper( n.name ) where name = n.name;
create trigger xr.rov_unane_u after update of name, uname on xr.repositoryobjectversion
referencing new as n
-for each row mode db2sql
+for each row
update xr.repositoryobjectversion set uname = upper( n.name )
where name = n.name and uname <> upper( n.name );
create unique index xr.versionname on xr.repositoryobjectversion (resourceid, versionName);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedureInTrigger.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedureInTrigger.sql?view=diff&rev=469102&r1=469101&r2=469102
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedureInTrigger.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedureInTrigger.sql Mon Oct 30 01:34:06 2006
@@ -134,7 +134,7 @@
--- tests
create trigger after_stmt_trig_no_sql AFTER insert on t2
- for each STATEMENT mode db2sql call proc_no_sql();
+ for each STATEMENT call proc_no_sql();
--- insert 2 rows. check that trigger is fired - procedure should be called once
insert into t2 values (1,2), (2,4);
--- check inserts are successful
@@ -156,7 +156,7 @@
select * from t2;
create trigger after_row_trig_no_sql AFTER delete on t2
- for each ROW mode db2sql call proc_no_sql();
+ for each ROW call proc_no_sql();
--- delete all rows. check that trigger is fired - procedure should be called 2 times
delete from t2;
--- check delete is successful
@@ -166,7 +166,7 @@
drop trigger after_row_trig_no_sql;
create trigger before_stmt_trig_no_sql no cascade BEFORE insert on t2
- for each STATEMENT mode db2sql call proc_no_sql();
+ for each STATEMENT call proc_no_sql();
--- insert 2 rows. check that trigger is fired - procedure should be called once
insert into t2 values (1,2), (2,4);
--- check inserts are successful
@@ -188,7 +188,7 @@
select * from t2;
create trigger before_row_trig_no_sql no cascade BEFORE delete on t2
- for each ROW mode db2sql call proc_no_sql();
+ for each ROW call proc_no_sql();
--- delete and check trigger fired. procedure called twice
delete from t2;
--- check delete is successful. t2 must be empty
@@ -199,14 +199,14 @@
insert into t2 values (1,2), (2,4);
create trigger after_row_trig_contains_sql AFTER update on t2
- for each ROW mode db2sql call proc_contains_sql();
+ for each ROW call proc_contains_sql();
--- update 2 rows. check that trigger is fired - procedure should be called twice
update t2 set x=x*2;
--- check updates are successful
select * from t2;
create trigger before_stmt_trig_contains_sql no cascade BEFORE delete on t2
- for each STATEMENT mode db2sql call proc_contains_sql();
+ for each STATEMENT call proc_contains_sql();
--- delete 2 rows. check that trigger is fired - procedure should be called once
delete from t2;
--- check delete is successful
@@ -218,7 +218,7 @@
--- create a row in t1 for use in select in the procedure
insert into t1 values (1, 'one');
create trigger after_stmt_trig_reads_sql AFTER insert on t2
- for each STATEMENT mode db2sql call proc_reads_sql(1);
+ for each STATEMENT call proc_reads_sql(1);
--- insert 2 rows. check that trigger is fired - procedure should be called once
insert into t2 values (1,2), (2,4);
--- check inserts are successful
@@ -226,7 +226,7 @@
drop trigger after_stmt_trig_reads_sql;
create trigger before_row_trig_reads_sql no cascade BEFORE delete on t2
- for each ROW mode db2sql call proc_reads_sql(1);
+ for each ROW call proc_reads_sql(1);
--- delete 2 rows. check that trigger is fired - procedure should be called twice
delete from t2;
--- check delete is successful
@@ -236,7 +236,7 @@
--- empty t1
delete from t1;
create trigger after_stmt_trig_modifies_sql_insert_op AFTER insert on t2
- for each STATEMENT mode db2sql call proc_modifies_sql_insert_op(1, 'one');
+ for each STATEMENT call proc_modifies_sql_insert_op(1, 'one');
--- insert 2 rows
insert into t2 values (1,2), (2,4);
--- check trigger is fired. insertRow should be called once
@@ -245,7 +245,7 @@
select * from t2;
create trigger after_row_trig_modifies_sql_update_op AFTER update of x on t2
- for each ROW mode db2sql call proc_modifies_sql_update_op(2);
+ for each ROW call proc_modifies_sql_update_op(2);
--- update all rows
update t2 set x=x*2;
--- check row trigger was fired. value of i should be 5
@@ -254,7 +254,7 @@
select * from t2;
create trigger after_stmt_trig_modifies_sql_delete_op AFTER delete on t2
- for each STATEMENT mode db2sql call proc_modifies_sql_delete_op(5);
+ for each STATEMENT call proc_modifies_sql_delete_op(5);
--- delete from t2
delete from t2;
--- check trigger is fired. table t1 should be empty
@@ -268,7 +268,7 @@
create trigger refer_new_row_trig AFTER insert on t2
REFERENCING NEW as new
- for each ROW mode db2sql call proc_modifies_sql_insert_op(new.x, 'new');
+ for each ROW call proc_modifies_sql_insert_op(new.x, 'new');
--- insert a row
insert into t2 values (25, 50);
--- check trigger is fired. insertRow should be called once
@@ -278,7 +278,7 @@
create trigger refer_old_row_trig AFTER delete on t2
REFERENCING OLD as old
- for each ROW mode db2sql call proc_modifies_sql_delete_op(old.x);
+ for each ROW call proc_modifies_sql_delete_op(old.x);
--- delete a row
delete from t2 where x=25;
--- check trigger is fired. deleteRow should be called once
@@ -292,12 +292,12 @@
--- create a before trigger that calls a procedure that modifies sql data.
--- trigger creation should fail
create trigger before_trig_modifies_sql no cascade BEFORE insert on t2
- for each STATEMENT mode db2sql call proc_modifies_sql_insert_op(1, 'one');
+ for each STATEMENT call proc_modifies_sql_insert_op(1, 'one');
--- in a BEFORE trigger, call a procedure which actually modifies SQL data
--- trigger creation will pass but firing should fail
create trigger bad_before_trig no cascade BEFORE insert on t2
- for each STATEMENT mode db2sql call proc_wrongly_defined_as_no_sql(50, 'fifty');
+ for each STATEMENT call proc_wrongly_defined_as_no_sql(50, 'fifty');
--- try to insert 2 rows
--- Bug DERBY-1629 -- in JDK 1.6 you only get 38001, not 38000
insert into t2 values (1,2), (2,4);
@@ -309,7 +309,7 @@
--- procedures which insert/update/delete into trigger table
create trigger insert_trig AFTER update on t1
- for each STATEMENT mode db2sql call proc_modifies_sql_insert_op(1, 'one');
+ for each STATEMENT call proc_modifies_sql_insert_op(1, 'one');
insert into t1 values(2, 'two');
update t1 set i=i+1;
--- Check that update and insert successful. t1 should have 2 rows
@@ -322,14 +322,14 @@
drop trigger insert_trig;
create trigger update_trig AFTER insert on t1
- for each STATEMENT mode db2sql call proc_modifies_sql_update_op(2);
+ for each STATEMENT call proc_modifies_sql_update_op(2);
insert into t1 values (4,'four');
--- Check that insert successful and trigger fired.
select * from t1;
drop trigger update_trig;
create trigger delete_trig AFTER insert on t1
- for each STATEMENT mode db2sql call proc_modifies_sql_delete_op(3);
+ for each STATEMENT call proc_modifies_sql_delete_op(3);
insert into t1 values (8,'eight');
--- Check that insert was successful and trigger was fired
select * from t1;
@@ -337,7 +337,7 @@
--- Procedures with schema name
create trigger call_proc_in_default_schema AFTER insert on t2
- for each STATEMENT mode db2sql call APP.proc_no_sql();
+ for each STATEMENT call APP.proc_no_sql();
--- insert 2 rows. check that trigger is fired - procedure should be called once
insert into t2 values (1,2), (2,4);
--- check inserts are successful
@@ -345,7 +345,7 @@
drop trigger call_proc_in_default_schema;
create trigger call_proc_in_default_schema no cascade BEFORE delete on t2
- for each ROW mode db2sql call APP.proc_no_sql();
+ for each ROW call APP.proc_no_sql();
--- delete 2 rows. check that trigger is fired - procedure should be called twice
delete from t2;
--- check delete is successful
@@ -353,7 +353,7 @@
drop trigger call_proc_in_default_schema;
create trigger call_proc_in_new_schema no cascade BEFORE insert on t2
- for each STATEMENT mode db2sql call new_schema.proc_in_new_schema();
+ for each STATEMENT call new_schema.proc_in_new_schema();
--- insert 2 rows. check that trigger is fired - procedure should be called once
insert into t2 values (1,2), (2,4);
--- check inserts are successful
@@ -361,7 +361,7 @@
drop trigger call_proc_in_new_schema;
create trigger call_proc_in_new_schema AFTER delete on t2
- for each ROW mode db2sql call new_schema.proc_in_new_schema();
+ for each ROW call new_schema.proc_in_new_schema();
--- delete 2 rows. check that trigger is fired - procedure should be called twice
delete from t2;
--- check delete is successful
@@ -370,47 +370,47 @@
--- non-existent procedure
create trigger call_non_existent_proc1 AFTER insert on t2
- for each ROW mode db2sql call non_existent_proc();
+ for each ROW call non_existent_proc();
select count(*) from SYS.SYSTRIGGERS where triggername='CALL_NON_EXISTENT_PROC1';
create trigger call_proc_with_non_existent_proc2 AFTER insert on t2
- for each ROW mode db2sql call new_schema.non_existent_proc();
+ for each ROW call new_schema.non_existent_proc();
select count(*) from SYS.SYSTRIGGERS where triggername='CALL_NON_EXISTENT_PROC2';
create trigger call_proc_in_non_existent_schema AFTER insert on t2
- for each ROW mode db2sql call non_existent_schema.non_existent_proc();
+ for each ROW call non_existent_schema.non_existent_proc();
select count(*) from SYS.SYSTRIGGERS where triggername='CALL_PROC_IN_NON_EXISTENT_SCHEMA';
create trigger call_proc_using_non_existent_method AFTER insert on t2
- for each ROW mode db2sql call proc_using_non_existent_method();
+ for each ROW call proc_using_non_existent_method();
select count(*) from SYS.SYSTRIGGERS where triggername='CALL_PROC_WITH_NON_EXISTENT_METHOD';
create trigger call_non_existent_proc1 no cascade BEFORE insert on t2
- for each ROW mode db2sql call non_existent_proc();
+ for each ROW call non_existent_proc();
select count(*) from SYS.SYSTRIGGERS where triggername='CALL_NON_EXISTENT_PROC1';
create trigger call_proc_with_non_existent_proc2 no cascade BEFORE insert on t2
- for each ROW mode db2sql call new_schema.non_existent_proc();
+ for each ROW call new_schema.non_existent_proc();
select count(*) from SYS.SYSTRIGGERS where triggername='CALL_NON_EXISTENT_PROC2';
create trigger call_proc_in_non_existent_schema no cascade BEFORE insert on t2
- for each ROW mode db2sql call non_existent_schema.non_existent_proc();
+ for each ROW call non_existent_schema.non_existent_proc();
select count(*) from SYS.SYSTRIGGERS where triggername='CALL_PROC_IN_NON_EXISTENT_SCHEMA';
create trigger call_proc_using_non_existent_method no cascade BEFORE insert on t2
- for each ROW mode db2sql call proc_using_non_existent_method();
+ for each ROW call proc_using_non_existent_method();
select count(*) from SYS.SYSTRIGGERS where triggername='CALL_PROC_WITH_NON_EXISTENT_METHOD';
--- triggers must not allow dynamic parameters (?)
create trigger update_trig AFTER insert on t1
- for each STATEMENT mode db2sql call proc_modifies_sql_update_op(?);
+ for each STATEMENT call proc_modifies_sql_update_op(?);
--- insert some rows into t2
insert into t2 values (1,2), (2,4);
--- use procedure with commit
create trigger commit_trig AFTER delete on t2
- for each STATEMENT mode db2sql call commit_proc();
+ for each STATEMENT call commit_proc();
--- should fail
delete from t2;
--- check delete failed
@@ -418,7 +418,7 @@
drop trigger commit_trig;
create trigger commit_trig no cascade BEFORE delete on t2
- for each STATEMENT mode db2sql call commit_proc();
+ for each STATEMENT call commit_proc();
--- should fail
delete from t2;
--- check delete failed
@@ -427,7 +427,7 @@
--- use procedure with rollback
create trigger rollback_trig AFTER delete on t2
- for each STATEMENT mode db2sql call rollback_proc();
+ for each STATEMENT call rollback_proc();
--- should fail
delete from t2;
--- check delete failed
@@ -435,7 +435,7 @@
drop trigger rollback_trig;
create trigger rollback_trig no cascade BEFORE delete on t2
- for each STATEMENT mode db2sql call rollback_proc();
+ for each STATEMENT call rollback_proc();
--- should fail
delete from t2;
--- check delete failed
@@ -444,7 +444,7 @@
--- use procedure which changes isolation level
create trigger set_isolation_trig AFTER delete on t2
- for each STATEMENT mode db2sql call set_isolation_proc();
+ for each STATEMENT call set_isolation_proc();
--- should fail
delete from t2;
--- check delete failed
@@ -452,7 +452,7 @@
drop trigger set_isolation_trig;
create trigger set_isolation_trig no cascade BEFORE delete on t2
- for each STATEMENT mode db2sql call set_isolation_proc();
+ for each STATEMENT call set_isolation_proc();
--- should fail
delete from t2;
--- check delete failed
@@ -461,7 +461,7 @@
--- call procedure that selects from same trigger table
create trigger select_from_trig_table AFTER insert on t1
- for each STATEMENT mode db2sql call proc_reads_sql(1);
+ for each STATEMENT call proc_reads_sql(1);
--- insert 2 rows. check that trigger is fired - procedure should be called once
insert into t1 values (10, 'ten');
--- check inserts are successful
@@ -469,7 +469,7 @@
drop trigger select_from_trig_table;
create trigger select_from_trig_table no cascade before delete on t1
- for each STATEMENT mode db2sql call proc_reads_sql(1);
+ for each STATEMENT call proc_reads_sql(1);
--- delete a row. check that trigger is fired - procedure should be called once
delete from t1 where i=10;
--- check delete is successful
@@ -478,7 +478,7 @@
--- use procedures which alter/drop trigger table and some other table
create trigger alter_table_trig AFTER delete on t1
- for each STATEMENT mode db2sql call alter_table_proc();
+ for each STATEMENT call alter_table_proc();
--- should fail
delete from t1;
--- check delete failed
@@ -486,7 +486,7 @@
drop trigger alter_table_trig;
create trigger drop_table_trig AFTER delete on t2
- for each STATEMENT mode db2sql call drop_table_proc();
+ for each STATEMENT call drop_table_proc();
--- should fail
delete from t2;
--- check delete failed
@@ -495,7 +495,7 @@
--- use procedures which create/drop trigger on trigger table and some other table
create trigger create_trigger_trig AFTER delete on t1
- for each STATEMENT mode db2sql call create_trigger_proc();
+ for each STATEMENT call create_trigger_proc();
--- should fail
delete from t1;
--- check delete failed
@@ -505,10 +505,10 @@
drop trigger create_trigger_trig;
--- create a trigger to test we cannot drop it from a procedure called by a trigger
-create trigger test_trig AFTER delete on t1 for each STATEMENT mode db2sql insert into t1 values(20, 'twenty');
+create trigger test_trig AFTER delete on t1 for each STATEMENT insert into t1 values(20, 'twenty');
create trigger drop_trigger_trig AFTER delete on t2
- for each STATEMENT mode db2sql call drop_trigger_proc();
+ for each STATEMENT call drop_trigger_proc();
--- should fail
delete from t2;
--- check delete failed
@@ -519,7 +519,7 @@
--- use procedures which create/drop index on trigger table and some other table
create trigger create_index_trig AFTER delete on t2
- for each STATEMENT mode db2sql call create_index_proc();
+ for each STATEMENT call create_index_proc();
--- should fail
delete from t2;
--- check delete failed
@@ -532,7 +532,7 @@
create index ix on t1(i,b);
create trigger drop_index_trig AFTER delete on t1
- for each STATEMENT mode db2sql call drop_index_proc();
+ for each STATEMENT call drop_index_proc();
--- should fail
delete from t1;
--- check delete failed