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 su...@apache.org on 2006/10/17 19:32:05 UTC
svn commit: r464970 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: suresht
Date: Tue Oct 17 10:32:03 2006
New Revision: 464970
URL: http://svn.apache.org/viewvc?view=rev&rev=464970
Log:
DERBY-1953 (Make FOR EACH clause and MODE DB2SQL in CREATE TRIGGER
statement optional)
Patch Contributed by Yip Ng.
This patch makes the FOR EACH clause optional in CREATE TRIGGER
statement. Since the changes are mainly in the trigger definition of
sqlgrammar.jj file and to make this backward compatibile with previous
releasesof Derby, I also addressed DERBY-1770, Make MODE DB2SQL optional.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?view=diff&rev=464970&r1=464969&r2=464970
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Tue Oct 17 10:32:03 2006
@@ -10018,7 +10018,7 @@
triggerDefinition() throws StandardException :
{
Boolean isBefore;
- Boolean isRow;
+ Boolean isRow = Boolean.FALSE; // STATEMENT implicit by default
TableName tableName;
TableName triggerName;
Token[] tokenHolder = new Token[1];
@@ -10040,8 +10040,8 @@
triggerEvent = triggerEvent(triggerColumns) // { INSERT | DELETE | UPDATE [ colList ] }
<ON> tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
[ refClause = triggerReferencingClause() ] // REFERENCING OLD/NEW AS
- <FOR> <EACH> isRow = rowOrStatement()
- <MODE> <DB2SQL>
+ [ <FOR> <EACH> isRow = rowOrStatement() ]
+ [ <MODE> <DB2SQL> ]
//we are not top level statement
actionNode = proceduralStatement(tokenHolder)
// the trigger body
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out?view=diff&rev=464970&r1=464969&r2=464970
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out Tue Oct 17 10:32:03 2006
@@ -1445,10 +1445,12 @@
ij> -- Beetle 5637: Require FOR EACH clause in DB2 mode. Optional in Cloudscape mode.
create table t1(i int);
0 rows inserted/updated/deleted
-ij> -- Should fail
-create trigger trig1 after insert on t1 mode db2sql values (8);
-ERROR 42X01: Syntax error: Encountered "mode" at line 2, column 41.
-ij> -- Should pass
+ij> -- DERBY-1953: The following statement will fail in DB2 LUW since it currently does
+-- not allow the FOR EACH part to be optional but DB2 iSeries allows both FOR EACH and
+-- MODE part to be optional. So this test is commented out for reference since it is
+-- not relevant after DERBY-1953 is applied (allow FOR EACH and MODE part to be optional).
+-- create trigger trig1 after insert on t1 mode db2sql values (8);
+-- Should pass
create trigger trig1 after insert on t1 for each row mode db2sql values (8);
0 rows inserted/updated/deleted
ij> create trigger trig2 after insert on t1 for each statement mode db2sql values (8);
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=464970&r1=464969&r2=464970
==============================================================================
--- 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 Tue Oct 17 10:32:03 2006
@@ -1303,4 +1303,85 @@
0 rows inserted/updated/deleted
ij(USER1)> drop schema ippo restrict;
0 rows inserted/updated/deleted
+ij(USER1)> -- DERBY-1953
+-- if neither FOR EACH STATEMENT or FOR EACH ROW is specified, FOR EACH STATEMENT is implicit.
+create table topt1 (i int);
+0 rows inserted/updated/deleted
+ij(USER1)> insert into topt1 values 1,2,3;
+3 rows inserted/updated/deleted
+ij(USER1)> create table topt2 (i int);
+0 rows inserted/updated/deleted
+ij(USER1)> -- expect error
+create trigger tropt after insert on topt1 for each mode db2sql insert into topt2 values 1;
+ERROR 42X01: Syntax error: Encountered "mode" at line 2, column 53.
+ij(USER1)> -- ok
+create trigger tropt after insert on topt1 insert into topt2 values 1;
+0 rows inserted/updated/deleted
+ij(USER1)> insert into topt1 values 4,5,6;
+3 rows inserted/updated/deleted
+ij(USER1)> -- expect 1 row
+select * from topt2;
+I
+-----------
+1
+ij(USER1)> drop trigger tropt;
+0 rows inserted/updated/deleted
+ij(USER1)> delete from topt2;
+1 row inserted/updated/deleted
+ij(USER1)> create trigger tropt after insert on topt1 referencing new_table as new_opt1 insert into topt2 select * from new_opt1;
+0 rows inserted/updated/deleted
+ij(USER1)> insert into topt1 values 7,8,9;
+3 rows inserted/updated/deleted
+ij(USER1)> -- expect 3 rows
+select * from topt2;
+I
+-----------
+7
+8
+9
+ij(USER1)> drop trigger tropt;
+0 rows inserted/updated/deleted
+ij(USER1)> delete from topt2;
+3 rows inserted/updated/deleted
+ij(USER1)> create trigger tropt after insert on topt1 referencing new_table as new_opt1 for each statement insert into topt2 select * from new_opt1;
+0 rows inserted/updated/deleted
+ij(USER1)> insert into topt1 values 10,11,12;
+3 rows inserted/updated/deleted
+ij(USER1)> -- expect 3 rows
+select * from topt2;
+I
+-----------
+10
+11
+12
+ij(USER1)> drop trigger tropt;
+0 rows inserted/updated/deleted
+ij(USER1)> delete from topt2;
+3 rows inserted/updated/deleted
+ij(USER1)> create trigger tropt after update on topt1 referencing old as oldrow for each row insert into topt2 values oldrow.i;
+0 rows inserted/updated/deleted
+ij(USER1)> update topt1 set i=100;
+12 rows inserted/updated/deleted
+ij(USER1)> -- expect 12 rows
+select * from topt2;
+I
+-----------
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+ij(USER1)> drop trigger tropt;
+0 rows inserted/updated/deleted
+ij(USER1)> drop table topt2;
+0 rows inserted/updated/deleted
+ij(USER1)> drop table topt1;
+0 rows inserted/updated/deleted
ij(USER1)>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql?view=diff&rev=464970&r1=464969&r2=464970
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql Tue Oct 17 10:32:03 2006
@@ -972,8 +972,11 @@
create table t1(i int);
--- Should fail
-create trigger trig1 after insert on t1 mode db2sql values (8);
+-- DERBY-1953: The following statement will fail in DB2 LUW since it currently does
+-- not allow the FOR EACH part to be optional but DB2 iSeries allows both FOR EACH and
+-- MODE part to be optional. So this test is commented out for reference since it is
+-- not relevant after DERBY-1953 is applied (allow FOR EACH and MODE part to be optional).
+-- create trigger trig1 after insert on t1 mode db2sql values (8);
-- Should pass
create trigger trig1 after insert on t1 for each row mode db2sql values (8);
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=464970&r1=464969&r2=464970
==============================================================================
--- 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 Tue Oct 17 10:32:03 2006
@@ -729,3 +729,37 @@
drop table ippo.t2;
drop table ippo.t1;
drop schema ippo restrict;
+
+-- DERBY-1953
+-- if neither FOR EACH STATEMENT or FOR EACH ROW is specified, FOR EACH STATEMENT is implicit.
+create table topt1 (i int);
+insert into topt1 values 1,2,3;
+create table topt2 (i int);
+-- expect error
+create trigger tropt after insert on topt1 for each mode db2sql insert into topt2 values 1;
+-- ok
+create trigger tropt after insert on topt1 insert into topt2 values 1;
+insert into topt1 values 4,5,6;
+-- expect 1 row
+select * from topt2;
+drop trigger tropt;
+delete from topt2;
+create trigger tropt after insert on topt1 referencing new_table as new_opt1 insert into topt2 select * from new_opt1;
+insert into topt1 values 7,8,9;
+-- expect 3 rows
+select * from topt2;
+drop trigger tropt;
+delete from topt2;
+create trigger tropt after insert on topt1 referencing new_table as new_opt1 for each statement insert into topt2 select * from new_opt1;
+insert into topt1 values 10,11,12;
+-- expect 3 rows
+select * from topt2;
+drop trigger tropt;
+delete from topt2;
+create trigger tropt after update on topt1 referencing old as oldrow for each row insert into topt2 values oldrow.i;
+update topt1 set i=100;
+-- expect 12 rows
+select * from topt2;
+drop trigger tropt;
+drop table topt2;
+drop table topt1;