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 ka...@apache.org on 2013/10/23 13:28:25 UTC
svn commit: r1534988 - in /db/derby/code/trunk/java:
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/
testing/org/apache/derbyTesting/functionTests/tests/tools/
tools/org/apache/...
Author: kahatlen
Date: Wed Oct 23 11:28:24 2013
New Revision: 1534988
URL: http://svn.apache.org/r1534988
Log:
DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements
Add dblook support for triggers with a WHEN clause.
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java
db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out?rev=1534988&r1=1534987&r2=1534988&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out Wed Oct 23 11:28:24 2013
@@ -1656,6 +1656,12 @@ T3
Table
-----
<systemid>
+<Trigger>APP.TRIGWHEN -> <Table>APP.T1
+Trigger
+T1
+Table
+-----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -1733,6 +1739,30 @@ StoredPreparedStatement
<systemid>
Conglomerate
-----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+-----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+-----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+-----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+-----
========== SYSFILES ==========
<systemid>
FOO
@@ -1854,6 +1884,26 @@ null
-----
<systemid>
<systemname>
+APP
+T
+<validityflag>
+values 1
+<systemid>
+APP
+null
+-----
+<systemid>
+<systemid>
+APP
+T
+<validityflag>
+VALUES exists (select * from new org.apache.derby.catalog.TriggerOldTransitionRows() OLD where i > 4)
+<systemid>
+APP
+null
+-----
+<systemid>
+<systemname>
"Quoted"Schema"
T
<validityflag>
@@ -2190,6 +2240,25 @@ null
null
-----
<systemid>
+TRIGWHEN
+APP
+<systemid>
+U
+A
+S
+E
+T1
+<systemid>
+<systemid>
+(1,2,3,4)
+values 1
+true
+false
+OLD
+null
+exists (select * from old where i > 4)
+-----
+<systemid>
TrigThree
APP
<systemid>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out?rev=1534988&r1=1534987&r2=1534988&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out Wed Oct 23 11:28:24 2013
@@ -1656,6 +1656,12 @@ T3
Table
-----
<systemid>
+<Trigger>APP.TRIGWHEN -> <Table>APP.T1
+Trigger
+T1
+Table
+-----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -1733,6 +1739,30 @@ StoredPreparedStatement
<systemid>
Conglomerate
-----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+-----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+-----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+-----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+-----
========== SYSFILES ==========
<systemid>
FOO
@@ -1854,6 +1884,26 @@ null
-----
<systemid>
<systemname>
+APP
+T
+<validityflag>
+values 1
+<systemid>
+APP
+null
+-----
+<systemid>
+<systemid>
+APP
+T
+<validityflag>
+VALUES exists (select * from new org.apache.derby.catalog.TriggerOldTransitionRows() OLD where i > 4)
+<systemid>
+APP
+null
+-----
+<systemid>
+<systemname>
"Quoted"Schema"
T
<validityflag>
@@ -2190,6 +2240,25 @@ null
null
-----
<systemid>
+TRIGWHEN
+APP
+<systemid>
+U
+A
+S
+E
+T1
+<systemid>
+<systemid>
+(1,2,3,4)
+values 1
+true
+false
+OLD
+null
+exists (select * from old where i > 4)
+-----
+<systemid>
TrigThree
APP
<systemid>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out?rev=1534988&r1=1534987&r2=1534988&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out Wed Oct 23 11:28:24 2013
@@ -1643,6 +1643,12 @@ T3
Table
----
<systemid>
+<Trigger>APP.TRIGWHEN -> <Table>APP.T1
+Trigger
+T1
+Table
+----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -1726,6 +1732,30 @@ StoredPreparedStatement
<systemid>
Conglomerate
----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
========== SYSFILES ==========
<systemid>
FOO
@@ -1847,6 +1877,26 @@ null
----
<systemid>
<systemname>
+APP
+T
+<validityflag>
+values 1
+<systemid>
+APP
+null
+----
+<systemid>
+<systemid>
+APP
+T
+<validityflag>
+VALUES exists (select * from new org.apache.derby.catalog.TriggerOldTransitionRows() OLD where i > 4)
+<systemid>
+APP
+null
+----
+<systemid>
+<systemname>
"Quoted"Schema"
T
<validityflag>
@@ -2183,6 +2233,25 @@ null
null
----
<systemid>
+TRIGWHEN
+APP
+<systemid>
+U
+A
+S
+E
+T1
+<systemid>
+<systemid>
+(1,2,3,4)
+values 1
+true
+false
+OLD
+null
+exists (select * from old where i > 4)
+----
+<systemid>
TrigThree
APP
<systemid>
@@ -3920,6 +3989,12 @@ T3
Table
----
<systemid>
+<Trigger>APP.TRIGWHEN -> <Table>APP.T1
+Trigger
+T1
+Table
+----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -3997,6 +4072,30 @@ StoredPreparedStatement
<systemid>
Conglomerate
----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
========== SYSFILES ==========
<systemid>
FOO
@@ -4118,6 +4217,26 @@ null
----
<systemid>
<systemname>
+APP
+T
+<validityflag>
+values 1
+<systemid>
+APP
+null
+----
+<systemid>
+<systemid>
+APP
+T
+<validityflag>
+VALUES exists (select * from new org.apache.derby.catalog.TriggerOldTransitionRows() OLD where i > 4)
+<systemid>
+APP
+null
+----
+<systemid>
+<systemname>
"Quoted"Schema"
T
<validityflag>
@@ -4454,6 +4573,25 @@ null
null
----
<systemid>
+TRIGWHEN
+APP
+<systemid>
+U
+A
+S
+E
+T1
+<systemid>
+<systemid>
+(1,2,3,4)
+values 1
+true
+false
+OLD
+null
+exists (select * from old where i > 4)
+----
+<systemid>
TrigThree
APP
<systemid>
@@ -5627,6 +5765,12 @@ T1
Table
----
<systemid>
+<Trigger>APP.TRIGWHEN -> <Table>APP.T1
+Trigger
+T1
+Table
+----
+<systemid>
<Trigger>Foo Bar.TRIG2 -> <StoredPreparedStatement>Foo Bar.<sysname>
Trigger
<systemid>
@@ -5644,6 +5788,30 @@ View
T1
ColumnsInTable
----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
========== SYSFILES ==========
========== SYSFOREIGNKEYS ==========
========== SYSKEYS ==========
@@ -5665,6 +5833,26 @@ APP
----
========== SYSSTATEMENTS ==========
<systemid>
+<systemid>
+APP
+T
+<validityflag>
+VALUES exists (select * from new org.apache.derby.catalog.TriggerOldTransitionRows() OLD where i > 4)
+<systemid>
+APP
+null
+----
+<systemid>
+<systemname>
+APP
+T
+<validityflag>
+values 1
+<systemid>
+APP
+null
+----
+<systemid>
<systemname>
Foo Bar
T
@@ -5731,6 +5919,25 @@ null
null
null
----
+<systemid>
+TRIGWHEN
+APP
+<systemid>
+U
+A
+S
+E
+T1
+<systemid>
+<systemid>
+(1,2,3,4)
+values 1
+true
+false
+OLD
+null
+exists (select * from old where i > 4)
+----
========== SYSVIEWS ==========
V2
create view v2 as select * from bar.t1
@@ -6083,6 +6290,7 @@ SET SCHEMA "APP"
-- DDL Statements for triggers
-- ----------------------------------------------
CREATE TRIGGER "APP"."TRIGONE" AFTER INSERT ON "BAR"."T1" FOR EACH ROW update bar.t1 set i = 4 where i = 2
+CREATE TRIGGER "APP"."TRIGTWO" AFTER INSERT ON "BAR"."T1" REFERENCING NEW AS NEW FOR EACH ROW WHEN (new.i > 4) delete from bar.t1 where i = 2
############## End File Contents ################
File dblook.log was empty.
************
@@ -6245,6 +6453,7 @@ SET SCHEMA "APP" #
-- DDL Statements for triggers
-- ----------------------------------------------
CREATE TRIGGER "APP"."TRIGONE" AFTER INSERT ON "BAR"."T1" FOR EACH ROW update bar.t1 set i = 4 where i = 2 #
+CREATE TRIGGER "APP"."TRIGTWO" AFTER INSERT ON "BAR"."T1" REFERENCING NEW AS NEW FOR EACH ROW WHEN (new.i > 4) delete from bar.t1 where i = 2 #
************
Msg Test 5
************
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out?rev=1534988&r1=1534987&r2=1534988&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out Wed Oct 23 11:28:24 2013
@@ -1643,6 +1643,12 @@ T3
Table
----
<systemid>
+<Trigger>APP.TRIGWHEN -> <Table>APP.T1
+Trigger
+T1
+Table
+----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -1726,6 +1732,30 @@ StoredPreparedStatement
<systemid>
Conglomerate
----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
========== SYSFILES ==========
<systemid>
FOO
@@ -1847,6 +1877,26 @@ null
----
<systemid>
<systemname>
+APP
+T
+<validityflag>
+values 1
+<systemid>
+APP
+null
+----
+<systemid>
+<systemid>
+APP
+T
+<validityflag>
+VALUES exists (select * from new org.apache.derby.catalog.TriggerOldTransitionRows() OLD where i > 4)
+<systemid>
+APP
+null
+----
+<systemid>
+<systemname>
"Quoted"Schema"
T
<validityflag>
@@ -2183,6 +2233,25 @@ null
null
----
<systemid>
+TRIGWHEN
+APP
+<systemid>
+U
+A
+S
+E
+T1
+<systemid>
+<systemid>
+(1,2,3,4)
+values 1
+true
+false
+OLD
+null
+exists (select * from old where i > 4)
+----
+<systemid>
TrigThree
APP
<systemid>
@@ -3920,6 +3989,12 @@ T3
Table
----
<systemid>
+<Trigger>APP.TRIGWHEN -> <Table>APP.T1
+Trigger
+T1
+Table
+----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -3997,6 +4072,30 @@ StoredPreparedStatement
<systemid>
Conglomerate
----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
========== SYSFILES ==========
<systemid>
FOO
@@ -4118,6 +4217,26 @@ null
----
<systemid>
<systemname>
+APP
+T
+<validityflag>
+values 1
+<systemid>
+APP
+null
+----
+<systemid>
+<systemid>
+APP
+T
+<validityflag>
+VALUES exists (select * from new org.apache.derby.catalog.TriggerOldTransitionRows() OLD where i > 4)
+<systemid>
+APP
+null
+----
+<systemid>
+<systemname>
"Quoted"Schema"
T
<validityflag>
@@ -4454,6 +4573,25 @@ null
null
----
<systemid>
+TRIGWHEN
+APP
+<systemid>
+U
+A
+S
+E
+T1
+<systemid>
+<systemid>
+(1,2,3,4)
+values 1
+true
+false
+OLD
+null
+exists (select * from old where i > 4)
+----
+<systemid>
TrigThree
APP
<systemid>
@@ -5627,6 +5765,12 @@ T1
Table
----
<systemid>
+<Trigger>APP.TRIGWHEN -> <Table>APP.T1
+Trigger
+T1
+Table
+----
+<systemid>
<Trigger>Foo Bar.TRIG2 -> <StoredPreparedStatement>Foo Bar.<sysname>
Trigger
<systemid>
@@ -5644,6 +5788,30 @@ View
T1
ColumnsInTable
----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<StoredPreparedStatement>APP.<sysname> -> <Table>APP.T1
+StoredPreparedStatement
+T1
+Table
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
+<systemid>
+<Trigger>APP.TRIGWHEN -> <StoredPreparedStatement>APP.<sysname>
+Trigger
+<systemid>
+StoredPreparedStatement
+----
========== SYSFILES ==========
========== SYSFOREIGNKEYS ==========
========== SYSKEYS ==========
@@ -5665,6 +5833,26 @@ APP
----
========== SYSSTATEMENTS ==========
<systemid>
+<systemid>
+APP
+T
+<validityflag>
+VALUES exists (select * from new org.apache.derby.catalog.TriggerOldTransitionRows() OLD where i > 4)
+<systemid>
+APP
+null
+----
+<systemid>
+<systemname>
+APP
+T
+<validityflag>
+values 1
+<systemid>
+APP
+null
+----
+<systemid>
<systemname>
Foo Bar
T
@@ -5731,6 +5919,25 @@ null
null
null
----
+<systemid>
+TRIGWHEN
+APP
+<systemid>
+U
+A
+S
+E
+T1
+<systemid>
+<systemid>
+(1,2,3,4)
+values 1
+true
+false
+OLD
+null
+exists (select * from old where i > 4)
+----
========== SYSVIEWS ==========
V2
create view v2 as select * from bar.t1
@@ -6083,6 +6290,7 @@ SET SCHEMA "APP"
-- DDL Statements for triggers
-- ----------------------------------------------
CREATE TRIGGER "APP"."TRIGONE" AFTER INSERT ON "BAR"."T1" FOR EACH ROW update bar.t1 set i = 4 where i = 2
+CREATE TRIGGER "APP"."TRIGTWO" AFTER INSERT ON "BAR"."T1" REFERENCING NEW AS NEW FOR EACH ROW WHEN (new.i > 4) delete from bar.t1 where i = 2
############## End File Contents ################
File dblook.log was empty.
************
@@ -6245,6 +6453,7 @@ SET SCHEMA "APP" #
-- DDL Statements for triggers
-- ----------------------------------------------
CREATE TRIGGER "APP"."TRIGONE" AFTER INSERT ON "BAR"."T1" FOR EACH ROW update bar.t1 set i = 4 where i = 2 #
+CREATE TRIGGER "APP"."TRIGTWO" AFTER INSERT ON "BAR"."T1" REFERENCING NEW AS NEW FOR EACH ROW WHEN (new.i > 4) delete from bar.t1 where i = 2 #
************
Msg Test 5
************
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql?rev=1534988&r1=1534987&r2=1534988&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql Wed Oct 23 11:28:24 2013
@@ -197,6 +197,9 @@ create table x (x int);
create table removed (x int);
create trigger trigFour after update of x on x referencing old_table as old new_table as new for each statement insert into removed select * from old where x not in (select x from new where x < 10);
+-- Test trigger with WHEN clause (DERBY-534).
+create trigger trigWhen after update on t1 referencing old table as old when (exists (select * from old where i > 4)) values 1;
+
-- ----------------------------------------------
-- Roles
-- ----------------------------------------------
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql?rev=1534988&r1=1534987&r2=1534988&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql Wed Oct 23 11:28:24 2013
@@ -88,6 +88,9 @@ create synonym syn1 for bar.t1;
create trigger trigOne after insert on bar.t1 for each row update bar.t1 set i = 4 where i = 2;
+-- trigger with WHEN clause (DERBY-534)
+create trigger trigTwo after insert on bar.t1 referencing new as new for each row when (new.i > 4) delete from bar.t1 where i = 2;
+
-- ----------------------------------------------
-- UDTs
-- ----------------------------------------------
Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java?rev=1534988&r1=1534987&r2=1534988&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java Wed Oct 23 11:28:24 2013
@@ -23,53 +23,70 @@ package org.apache.derby.impl.tools.dblo
import java.sql.Connection;
import java.sql.Statement;
-import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
-import java.util.HashMap;
-import java.util.StringTokenizer;
-
import org.apache.derby.tools.dblook;
public class DB_Trigger {
- /* ************************************************
+ // Column name constants for SYS.SYSTRIGGERS
+ private static final String TRIGGERNAME = "TRIGGERNAME";
+ private static final String SCHEMAID = "SCHEMAID";
+ private static final String EVENT = "EVENT";
+ private static final String FIRINGTIME = "FIRINGTIME";
+ private static final String TYPE = "TYPE";
+ private static final String TABLEID = "TABLEID";
+ private static final String REFERENCEDCOLUMNS = "REFERENCEDCOLUMNS";
+ private static final String TRIGGERDEFINITION = "TRIGGERDEFINITION";
+ private static final String REFERENCINGOLD = "REFERENCINGOLD";
+ private static final String REFERENCINGNEW = "REFERENCINGNEW";
+ private static final String OLDREFERENCINGNAME = "OLDREFERENCINGNAME";
+ private static final String NEWREFERENCINGNAME = "NEWREFERENCINGNAME";
+ private static final String WHENCLAUSETEXT = "WHENCLAUSETEXT";
+
+ /** ************************************************
* Generate the DDL for all triggers in a given
* database.
* @param conn Connection to the source database.
- * @return The DDL for the triggers has been written
- * to output via Logs.java.
+ * @param supportsWhenClause Tells whether the database supports the
+ * trigger WHEN clause.
****/
- public static void doTriggers (Connection conn)
+ public static void doTriggers(Connection conn, boolean supportsWhenClause)
throws SQLException
{
Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery("SELECT TRIGGERNAME, SCHEMAID, " +
- "EVENT, FIRINGTIME, TYPE, TABLEID, REFERENCEDCOLUMNS, " +
- "TRIGGERDEFINITION, REFERENCINGOLD, REFERENCINGNEW, OLDREFERENCINGNAME, " +
- "NEWREFERENCINGNAME FROM SYS.SYSTRIGGERS WHERE STATE != 'D'");
+ ResultSet rs = stmt.executeQuery(
+ "SELECT * FROM SYS.SYSTRIGGERS WHERE STATE != 'D'");
boolean firstTime = true;
while (rs.next()) {
String trigName = dblook.addQuotes(
- dblook.expandDoubleQuotes(rs.getString(1)));
- String trigSchema = dblook.lookupSchemaId(rs.getString(2));
+ dblook.expandDoubleQuotes(rs.getString(TRIGGERNAME)));
+ String trigSchema = dblook.lookupSchemaId(rs.getString(SCHEMAID));
if (dblook.isIgnorableSchema(trigSchema))
continue;
trigName = trigSchema + "." + trigName;
- String tableName = dblook.lookupTableId(rs.getString(6));
+ String tableName = dblook.lookupTableId(rs.getString(TABLEID));
+
+ // Get the WHEN clause text, if there is a WHEN clause. The
+ // WHENCLAUSETEXT column is only present if the data dictionary
+ // version is 10.11 or higher (DERBY-534).
+ String whenClause =
+ supportsWhenClause ? rs.getString(WHENCLAUSETEXT) : null;
// We'll write the DDL for this trigger if either 1) it is on
// a table in the user-specified list, OR 2) the trigger text
// contains a reference to a table in the user-specified list.
- if (!dblook.stringContainsTargetTable(rs.getString(8)) &&
+ if (!dblook.stringContainsTargetTable(
+ rs.getString(TRIGGERDEFINITION)) &&
+ !dblook.stringContainsTargetTable(whenClause) &&
(dblook.isExcludedTable(tableName)))
continue;
@@ -80,7 +97,7 @@ public class DB_Trigger {
}
String createTrigString = createTrigger(trigName,
- tableName, rs);
+ tableName, whenClause, rs);
Logs.writeToNewDDL(createTrigString);
Logs.writeStmtEndToNewDDL();
@@ -94,37 +111,40 @@ public class DB_Trigger {
}
- /* ************************************************
+ /** ************************************************
* Generate DDL for a specific trigger.
* @param trigName Name of the trigger.
* @param tableName Name of the table on which the trigger
* fires.
+ * @param whenClause The WHEN clause text (possibly {@code null}).
* @param aTrig Information about the trigger.
* @return The DDL for the current trigger is returned
* as a String.
****/
private static String createTrigger(String trigName, String tableName,
- ResultSet aTrig) throws SQLException
+ String whenClause, ResultSet aTrig) throws SQLException
{
- StringBuffer sb = new StringBuffer ("CREATE TRIGGER ");
+ StringBuilder sb = new StringBuilder("CREATE TRIGGER ");
sb.append(trigName);
// Firing time.
- if (aTrig.getString(4).charAt(0) == 'A')
+ if (aTrig.getString(FIRINGTIME).charAt(0) == 'A') {
sb.append(" AFTER ");
- else
+ } else {
sb.append(" NO CASCADE BEFORE ");
+ }
// Event.
- switch (aTrig.getString(3).charAt(0)) {
+ String event = aTrig.getString(EVENT);
+ switch (event.charAt(0)) {
case 'I': sb.append("INSERT");
break;
case 'D': sb.append("DELETE");
break;
case 'U': sb.append("UPDATE");
- String updateCols = aTrig.getString(7);
+ String updateCols = aTrig.getString(REFERENCEDCOLUMNS);
//DERBY-5839 dblook run on toursdb fails on triggers
// with java.lang.StringIndexOutOfBoundsException in
// dblook.log
@@ -145,12 +165,12 @@ public class DB_Trigger {
if (!aTrig.wasNull() && !updateCols.equals("NULL")) {
sb.append(" OF ");
sb.append(dblook.getColumnListFromDescription(
- aTrig.getString(6), updateCols));
+ aTrig.getString(TABLEID), updateCols));
}
break;
default: // shouldn't happen.
Logs.debug("INTERNAL ERROR: unexpected trigger event: " +
- aTrig.getString(3), (String)null);
+ event, (String)null);
break;
}
@@ -159,12 +179,12 @@ public class DB_Trigger {
sb.append(tableName);
// Referencing...
- char trigType = aTrig.getString(5).charAt(0);
- String oldReferencing = aTrig.getString(11);
- String newReferencing = aTrig.getString(12);
+ char trigType = aTrig.getString(TYPE).charAt(0);
+ String oldReferencing = aTrig.getString(OLDREFERENCINGNAME);
+ String newReferencing = aTrig.getString(NEWREFERENCINGNAME);
if ((oldReferencing != null) || (newReferencing != null)) {
sb.append(" REFERENCING");
- if (aTrig.getBoolean(9)) {
+ if (aTrig.getBoolean(REFERENCINGOLD)) {
sb.append(" OLD");
if (trigType == 'S')
// Statement triggers work on tables.
@@ -174,7 +194,7 @@ public class DB_Trigger {
sb.append(" AS ");
sb.append(oldReferencing);
}
- if (aTrig.getBoolean(10)) {
+ if (aTrig.getBoolean(REFERENCINGNEW)) {
sb.append(" NEW");
if (trigType == 'S')
// Statement triggers work on tables.
@@ -193,8 +213,15 @@ public class DB_Trigger {
else
sb.append("ROW ");
- // Finally, the trigger action.
- sb.append(dblook.removeNewlines(aTrig.getString(8)));
+ // Finally, the trigger action, which consists of an optional WHEN
+ // clause and the triggered SQL statement.
+ if (whenClause != null) {
+ sb.append("WHEN (");
+ sb.append(dblook.removeNewlines(whenClause));
+ sb.append(") ");
+ }
+ sb.append(dblook.removeNewlines(aTrig.getString(TRIGGERDEFINITION)));
+
return sb.toString();
}
Modified: db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java?rev=1534988&r1=1534987&r2=1534988&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/tools/dblook.java Wed Oct 23 11:28:24 2013
@@ -30,7 +30,6 @@ import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
-import java.sql.SQLWarning;
import java.sql.Timestamp;
import java.util.HashMap;
@@ -519,6 +518,7 @@ public final class dblook {
boolean at10_6 = atVersion( conn, 10, 6 );
boolean at10_9 = atVersion( conn, 10, 9 );
+ boolean at10_11 = atVersion(conn, 10, 11);
// Generate DDL.
@@ -544,7 +544,7 @@ public final class dblook {
if (!skipViews)
DB_View.doViews(this.conn);
- DB_Trigger.doTriggers(this.conn);
+ DB_Trigger.doTriggers(this.conn, at10_11);
DB_Roles.doRoles(this.conn);
DB_GrantRevoke.doAuthorizations(this.conn, at10_6);