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/11/25 09:59:24 UTC
svn commit: r1545179 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/sql/dictionary/
engine/org/apache/derby/impl/sql/compile/
engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/master/ testing/org/apac...
Author: kahatlen
Date: Mon Nov 25 08:59:24 2013
New Revision: 1545179
URL: http://svn.apache.org/r1545179
Log:
DERBY-2041: Trigger should register a dependency on tables and columns used in its body
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
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/master/refActions.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerRecursion.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DropTableTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RenameTableTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRecursion.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java Mon Nov 25 08:59:24 2013
@@ -773,7 +773,8 @@ public class TriggerDescriptor extends U
switch (action)
{
/*
- ** We are only dependent on the underlying table, and our spses and
+ ** We are dependent on the underlying table, our SPSs, any tables
+ ** or other SQL objects that are referenced from the trigger, and
** privileges on various objects. (we should be dropped before our
** table is dropped. Also, we should be dropped before revoke
** RESTRICT privilege is issued otherwise revoke RESTRICT will
@@ -791,8 +792,10 @@ public class TriggerDescriptor extends U
case DependencyManager.DROP_TABLE:
case DependencyManager.DROP_SYNONYM:
case DependencyManager.DROP_SPS:
+ case DependencyManager.DROP_VIEW:
case DependencyManager.RENAME:
case DependencyManager.REVOKE_PRIVILEGE_RESTRICT:
+ case DependencyManager.DROP_METHOD_ALIAS:
DependencyManager dm = getDataDictionary().getDependencyManager();
throw StandardException.newException(SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
dm.getActionString(action),
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java Mon Nov 25 08:59:24 2013
@@ -36,6 +36,9 @@ import org.apache.derby.iapi.sql.compile
import org.apache.derby.iapi.sql.compile.Visitable;
import org.apache.derby.iapi.sql.conn.Authorizer;
import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
+import org.apache.derby.iapi.sql.depend.DependencyManager;
+import org.apache.derby.iapi.sql.depend.ProviderInfo;
+import org.apache.derby.iapi.sql.depend.ProviderList;
import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor;
import org.apache.derby.iapi.sql.dictionary.ColumnDescriptorList;
import org.apache.derby.iapi.sql.dictionary.DataDictionary;
@@ -69,6 +72,7 @@ class CreateTriggerNode extends DDLState
private final String originalActionText;
private final int whenOffset;
private final int actionOffset;
+ private ProviderInfo[] providerInfo;
private SchemaDescriptor triggerSchemaDescriptor;
private SchemaDescriptor compSchemaDescriptor;
@@ -370,9 +374,15 @@ class CreateTriggerNode extends DDLState
*/
boolean needInternalSQL = bindReferencesClause(dd);
+ ProviderList prevAPL =
+ compilerContext.getCurrentAuxiliaryProviderList();
+ ProviderList apl = new ProviderList();
+
lcc.pushTriggerTable(triggerTableDescriptor);
try
{
+ compilerContext.setCurrentAuxiliaryProviderList(apl);
+
/*
** Bind the trigger action and the trigger
** when clause to make sure that they are
@@ -406,6 +416,7 @@ class CreateTriggerNode extends DDLState
finally
{
lcc.popTriggerTable(triggerTableDescriptor);
+ compilerContext.setCurrentAuxiliaryProviderList(prevAPL);
}
/*
@@ -445,6 +456,11 @@ class CreateTriggerNode extends DDLState
if (referencesSessionSchema()) {
throw StandardException.newException(SQLState.LANG_OPERATION_NOT_ALLOWED_ON_SESSION_SCHEMA_TABLES);
}
+
+ DependencyManager dm = dd.getDependencyManager();
+ providerInfo = dm.getPersistentProviderInfos(apl);
+ dm.clearColumnInfoInProviders(apl);
+
}
/**
@@ -934,7 +950,8 @@ class CreateTriggerNode extends DDLState
oldTableInReferencingClause,
newTableInReferencingClause,
oldReferencingName,
- newReferencingName
+ newReferencingName,
+ providerInfo
);
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java Mon Nov 25 08:59:24 2013
@@ -34,19 +34,15 @@ import org.apache.derby.iapi.sql.diction
import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
import org.apache.derby.iapi.sql.dictionary.TriggerDescriptor;
-import org.apache.derby.iapi.types.DataValueFactory;
-
import org.apache.derby.iapi.sql.depend.DependencyManager;
-
-import org.apache.derby.iapi.sql.execute.ExecutionFactory;
+import org.apache.derby.iapi.sql.depend.Provider;
+import org.apache.derby.iapi.sql.depend.ProviderInfo;
import org.apache.derby.iapi.sql.Activation;
import org.apache.derby.iapi.error.StandardException;
import org.apache.derby.iapi.reference.SQLState;
-import org.apache.derby.iapi.services.context.ContextService;
-
import org.apache.derby.shared.common.sanity.SanityManager;
import org.apache.derby.catalog.UUID;
@@ -83,6 +79,7 @@ class CreateTriggerConstantAction extend
private Timestamp creationTimestamp;
private int[] referencedCols;
private int[] referencedColsInTriggerAction;
+ private final ProviderInfo[] providerInfo;
// CONSTRUCTORS
@@ -115,6 +112,7 @@ class CreateTriggerConstantAction extend
* @param referencingNew whether or not NEW appears in REFERENCING clause
* @param oldReferencingName old referencing table name, if any, that appears in REFERENCING clause
* @param newReferencingName new referencing table name, if any, that appears in REFERENCING clause
+ * @param providerInfo array of providers that the trigger depends on
*/
CreateTriggerConstantAction
(
@@ -138,7 +136,8 @@ class CreateTriggerConstantAction extend
boolean referencingOld,
boolean referencingNew,
String oldReferencingName,
- String newReferencingName
+ String newReferencingName,
+ ProviderInfo[] providerInfo
)
{
super(triggerTable.getUUID());
@@ -163,6 +162,7 @@ class CreateTriggerConstantAction extend
this.referencingNew = referencingNew;
this.oldReferencingName = oldReferencingName;
this.newReferencingName = newReferencingName;
+ this.providerInfo = providerInfo;
if (SanityManager.DEBUG)
{
SanityManager.ASSERT(triggerSchemaName != null, "triggerSchemaName sd is null");
@@ -359,6 +359,15 @@ class CreateTriggerConstantAction extend
}
dm.addDependency(triggerd, actionspsd, lcc.getContextManager());
dm.addDependency(triggerd, triggerTable, lcc.getContextManager());
+
+ // Make the TriggerDescriptor dependent on all objects referenced
+ // from the triggered statement or the WHEN clause.
+ for (ProviderInfo info : providerInfo) {
+ Provider provider = (Provider) info.getDependableFinder()
+ .getDependable(dd, info.getObjectId());
+ dm.addDependency(triggerd, provider, lcc.getContextManager());
+ }
+
//store trigger's dependency on various privileges in the dependeny system
storeViewTriggerDependenciesOnPrivileges(activation, triggerd);
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java Mon Nov 25 08:59:24 2013
@@ -1002,6 +1002,7 @@ public class GenericConstantActionFactor
* @param referencingNew whether or not NEW appears in REFERENCING clause
* @param oldReferencingName old referencing table name, if any, that appears in REFERCING clause
* @param newReferencingName new referencing table name, if any, that appears in REFERCING clause
+ * @param providerInfo array of providers that the trigger depends on
*/
public ConstantAction getCreateTriggerConstantAction
(
@@ -1025,7 +1026,8 @@ public class GenericConstantActionFactor
boolean referencingOld,
boolean referencingNew,
String oldReferencingName,
- String newReferencingName
+ String newReferencingName,
+ ProviderInfo[] providerInfo
)
{
return new CreateTriggerConstantAction(triggerSchemaName, triggerName,
@@ -1033,7 +1035,8 @@ public class GenericConstantActionFactor
whenText, actionSPSId, actionText, spsCompSchemaId, creationTimestamp,
referencedCols, referencedColsInTriggerAction,
originalWhenText, originalActionText,
- referencingOld, referencingNew, oldReferencingName, newReferencingName);
+ referencingOld, referencingNew,
+ oldReferencingName, newReferencingName, providerInfo);
}
/**
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=1545179&r1=1545178&r2=1545179&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 Mon Nov 25 08:59:24 2013
@@ -1632,18 +1632,36 @@ tee""Hee
Table
-----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Conglomerate>APP.<sysname>
+Trigger
+<systemid>
+Conglomerate
+-----
+<systemid>
<Trigger>APP.TRIGFOUR -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
StoredPreparedStatement
-----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Table>APP.REMOVED
+Trigger
+REMOVED
+Table
+-----
+<systemid>
<Trigger>APP.TRIGFOUR -> <Table>APP.X
Trigger
X
Table
-----
<systemid>
+<Trigger>APP.TRIGONE -> <ColumnsInTable>BAR.T4
+Trigger
+T4
+ColumnsInTable
+-----
+<systemid>
<Trigger>APP.TRIGONE -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -1662,6 +1680,12 @@ T1
Table
-----
<systemid>
+<Trigger>APP.TrigThree -> <ColumnsInTable>BAR.tWithKeys
+Trigger
+tWithKeys
+ColumnsInTable
+-----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<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=1545179&r1=1545178&r2=1545179&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 Mon Nov 25 08:59:24 2013
@@ -1632,18 +1632,36 @@ tee""Hee
Table
-----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Conglomerate>APP.<sysname>
+Trigger
+<systemid>
+Conglomerate
+-----
+<systemid>
<Trigger>APP.TRIGFOUR -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
StoredPreparedStatement
-----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Table>APP.REMOVED
+Trigger
+REMOVED
+Table
+-----
+<systemid>
<Trigger>APP.TRIGFOUR -> <Table>APP.X
Trigger
X
Table
-----
<systemid>
+<Trigger>APP.TRIGONE -> <ColumnsInTable>BAR.T4
+Trigger
+T4
+ColumnsInTable
+-----
+<systemid>
<Trigger>APP.TRIGONE -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -1662,6 +1680,12 @@ T1
Table
-----
<systemid>
+<Trigger>APP.TrigThree -> <ColumnsInTable>BAR.tWithKeys
+Trigger
+tWithKeys
+ColumnsInTable
+-----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<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=1545179&r1=1545178&r2=1545179&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 Mon Nov 25 08:59:24 2013
@@ -1619,18 +1619,36 @@ tee""Hee
Table
----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Conglomerate>APP.<sysname>
+Trigger
+<systemid>
+Conglomerate
+----
+<systemid>
<Trigger>APP.TRIGFOUR -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
StoredPreparedStatement
----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Table>APP.REMOVED
+Trigger
+REMOVED
+Table
+----
+<systemid>
<Trigger>APP.TRIGFOUR -> <Table>APP.X
Trigger
X
Table
----
<systemid>
+<Trigger>APP.TRIGONE -> <ColumnsInTable>BAR.T4
+Trigger
+T4
+ColumnsInTable
+----
+<systemid>
<Trigger>APP.TRIGONE -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -1649,6 +1667,12 @@ T1
Table
----
<systemid>
+<Trigger>APP.TrigThree -> <ColumnsInTable>BAR.tWithKeys
+Trigger
+tWithKeys
+ColumnsInTable
+----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -3965,18 +3989,36 @@ tee""Hee
Table
----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Conglomerate>APP.<sysname>
+Trigger
+<systemid>
+Conglomerate
+----
+<systemid>
<Trigger>APP.TRIGFOUR -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
StoredPreparedStatement
----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Table>APP.REMOVED
+Trigger
+REMOVED
+Table
+----
+<systemid>
<Trigger>APP.TRIGFOUR -> <Table>APP.X
Trigger
X
Table
----
<systemid>
+<Trigger>APP.TRIGONE -> <ColumnsInTable>BAR.T4
+Trigger
+T4
+ColumnsInTable
+----
+<systemid>
<Trigger>APP.TRIGONE -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -3995,6 +4037,12 @@ T1
Table
----
<systemid>
+<Trigger>APP.TrigThree -> <ColumnsInTable>BAR.tWithKeys
+Trigger
+tWithKeys
+ColumnsInTable
+----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
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=1545179&r1=1545178&r2=1545179&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 Mon Nov 25 08:59:24 2013
@@ -1619,18 +1619,36 @@ tee""Hee
Table
----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Conglomerate>APP.<sysname>
+Trigger
+<systemid>
+Conglomerate
+----
+<systemid>
<Trigger>APP.TRIGFOUR -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
StoredPreparedStatement
----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Table>APP.REMOVED
+Trigger
+REMOVED
+Table
+----
+<systemid>
<Trigger>APP.TRIGFOUR -> <Table>APP.X
Trigger
X
Table
----
<systemid>
+<Trigger>APP.TRIGONE -> <ColumnsInTable>BAR.T4
+Trigger
+T4
+ColumnsInTable
+----
+<systemid>
<Trigger>APP.TRIGONE -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -1649,6 +1667,12 @@ T1
Table
----
<systemid>
+<Trigger>APP.TrigThree -> <ColumnsInTable>BAR.tWithKeys
+Trigger
+tWithKeys
+ColumnsInTable
+----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -3965,18 +3989,36 @@ tee""Hee
Table
----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Conglomerate>APP.<sysname>
+Trigger
+<systemid>
+Conglomerate
+----
+<systemid>
<Trigger>APP.TRIGFOUR -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
StoredPreparedStatement
----
<systemid>
+<Trigger>APP.TRIGFOUR -> <Table>APP.REMOVED
+Trigger
+REMOVED
+Table
+----
+<systemid>
<Trigger>APP.TRIGFOUR -> <Table>APP.X
Trigger
X
Table
----
<systemid>
+<Trigger>APP.TRIGONE -> <ColumnsInTable>BAR.T4
+Trigger
+T4
+ColumnsInTable
+----
+<systemid>
<Trigger>APP.TRIGONE -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
@@ -3995,6 +4037,12 @@ T1
Table
----
<systemid>
+<Trigger>APP.TrigThree -> <ColumnsInTable>BAR.tWithKeys
+Trigger
+tWithKeys
+ColumnsInTable
+----
+<systemid>
<Trigger>APP.TrigThree -> <StoredPreparedStatement>APP.<sysname>
Trigger
<systemid>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions.out?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions.out Mon Nov 25 08:59:24 2013
@@ -378,14 +378,14 @@ X
ij> select * from t3;
Y
-----------
-ij> drop table t4;
-0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
+ij> drop table t4;
+0 rows inserted/updated/deleted
ij> --test for multiple fkeys on the same table referrring to
--different columns on the parent table.
create table t1(a int not null unique , b int not null unique);
@@ -827,14 +827,14 @@ Y
NULL
NULL
NULL
-ij> drop table t4;
-0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
+ij> drop table t4;
+0 rows inserted/updated/deleted
ij> --test for multiple fkeys on the same table referrring to
--different columns on the parent table.
create table t1(a int not null unique , b int not null unique);
@@ -942,12 +942,19 @@ ij> -- should fail
-- parent row can not be deleted because of a dependent relationship from another table
delete from t1 where a =1;
ERROR 23503: DELETE on table 'T1' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (1). The statement has been rolled back.
+ij> drop trigger trig_delete;
+0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> --do the same case as above with RESTRICT
--we should get error, because RESTRICT rules are checked before firing triggers
create table t2(x int references t1(a) ON DELETE RESTRICT , y int);
0 rows inserted/updated/deleted
+ij> create trigger trig_delete after DELETE on t1
+referencing old as deletedrow
+for each row
+delete from t2 where x = deletedrow.a;
+0 rows inserted/updated/deleted
ij> insert into t2 values(1, 2);
1 row inserted/updated/deleted
ij> insert into t2 values(2, 3);
@@ -955,6 +962,8 @@ ij> insert into t2 values(2, 3);
ij> --following delete should throw constraint violations error
delete from t1 where a =1;
ERROR 23503: DELETE on table 'T1' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (1). The statement has been rolled back.
+ij> drop trigger trig_delete;
+0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
@@ -997,6 +1006,8 @@ X |Y
2 |2
2 |3
ij> rollback;
+ij> drop trigger trig_update;
+0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> commit;
@@ -1004,6 +1015,10 @@ ij> --do the same case as above with RES
--we should get error, because RESTRICT is check before firing triggers
create table t2(x int references t1(a) ON UPDATE RESTRICT , y int);
0 rows inserted/updated/deleted
+ij> create trigger trig_update after UPDATE on t1
+referencing old as old for each row
+update t2 set x = 2 where x = old.a;
+0 rows inserted/updated/deleted
ij> insert into t2 values(1, 2);
1 row inserted/updated/deleted
ij> insert into t2 values(2, 3);
@@ -1023,6 +1038,8 @@ X |Y
1 |2
2 |3
ij> autocommit on;
+ij> drop trigger trig_update;
+0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
@@ -1066,14 +1083,14 @@ X
ij> select * from t3;
Y
-----------
-ij> drop table t4;
-0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
+ij> drop table t4;
+0 rows inserted/updated/deleted
ij> --After Statement triggers on the dependen tables
create table t1( a int not null primary key , b int , c int not null unique) ;
0 rows inserted/updated/deleted
@@ -1113,14 +1130,14 @@ X
ij> select * from t3;
Y
-----------
-ij> drop table t4;
-0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
+ij> drop table t4;
+0 rows inserted/updated/deleted
ij> --After triggers on a self referencing table
create table emp(empno char(2) not null, mgr char(2), constraint emp primary key(empno),
constraint manages foreign key(mgr) references emp(empno) on delete cascade);
@@ -1320,14 +1337,14 @@ L |M |OP
2 |1 |ad
6 |7 |ad
7 |6 |ad
-ij> drop table t1temp;
-0 rows inserted/updated/deleted
ij> alter table t1 drop constraint c1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
+ij> drop table t1temp;
+0 rows inserted/updated/deleted
ij> -- triggers on a cyclic referential actions
create table t1(a int not null primary key, b int not null unique);
0 rows inserted/updated/deleted
@@ -1427,14 +1444,14 @@ L |M |OP
2 |1 |bd
6 |7 |bd
7 |6 |bd
-ij> drop table t1temp;
-0 rows inserted/updated/deleted
ij> alter table t1 drop constraint c1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
+ij> drop table t1temp;
+0 rows inserted/updated/deleted
ij> --ROW triggers on a cyclic referential actions
create table t1(a int not null primary key, b int not null unique);
0 rows inserted/updated/deleted
@@ -1547,14 +1564,14 @@ L |M |OP
4 |3 |bd
6 |7 |bd
7 |6 |bd
-ij> drop table t1temp;
-0 rows inserted/updated/deleted
ij> alter table t1 drop constraint c1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
+ij> drop table t1temp;
+0 rows inserted/updated/deleted
ij> --SET NULL UPDATE STETEMENT triggers on a self referencing table
create table emp(empno char(2) not null, mgr char(2), constraint emp primary key(empno),
constraint manages foreign key(mgr) references emp(empno) on delete set null);
@@ -2529,6 +2546,10 @@ X
ij> select * from t3;
Y
-----------
+ij> drop trigger trig_delete;
+0 rows inserted/updated/deleted
+ij> drop trigger trig_delete1;
+0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t2;
@@ -2619,6 +2640,10 @@ Y
21
24
27
+ij> drop trigger trig_delete;
+0 rows inserted/updated/deleted
+ij> drop trigger trig_delete1;
+0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t2;
@@ -2825,14 +2850,14 @@ Z |OP
----------------
1 |bd
2 |bd
-ij> drop table t4;
-0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
+ij> drop table t4;
+0 rows inserted/updated/deleted
ij> ---multiple foreign keys pointing to the same table and has dependens
-- first foreign key path has zero rows qualified(bug 5197 from webshphere)
CREATE SCHEMA DB2ADMIN;
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?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- 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 Nov 25 08:59:24 2013
@@ -989,7 +989,7 @@ I
1
ij> drop trigger tt;
0 rows inserted/updated/deleted
-ij> -- dropping and recreating a table which the trigger references
+ij> -- dropping a table which the trigger references, should fail (DERBY-2041)
create table t3 (i int);
0 rows inserted/updated/deleted
ij> create table t4 (i int);
@@ -1003,17 +1003,14 @@ I
-----------
1
ij> drop table t4;
-0 rows inserted/updated/deleted
-ij> insert into t3 values 1;
-ERROR 42X05: Table/View 'T4' does not exist.
-ij> create table t4 (i int);
-0 rows inserted/updated/deleted
+ERROR X0Y25: Operation 'DROP TABLE' cannot be performed on object 'T4' because TRIGGER 'TT2' is dependent on that object.
ij> insert into t3 values 1;
1 row inserted/updated/deleted
ij> select * from t4;
I
-----------
1
+1
ij> -- dropping a function which the trigger references
create function max_value(x int, y int) returns int language java parameter style java external name 'java.lang.Math.max';
0 rows inserted/updated/deleted
@@ -1023,13 +1020,13 @@ ij> create trigger test_trigger AFTER in
0 rows inserted/updated/deleted
ij> insert into test values(1);
1 row inserted/updated/deleted
-ij> --- drop function and again do inserts. these should not work as the trigger would be invalid
+ij> --- drop function should fail (DERBY-2041)
drop function max_value;
-0 rows inserted/updated/deleted
+ERROR X0Y25: Operation 'DROP ROUTINE' cannot be performed on object 'MAX_VALUE' because TRIGGER 'TEST_TRIGGER' is dependent on that object.
ij> insert into test values(2);
-ERROR 42Y03: 'MAX_VALUE' is not recognized as a function or procedure.
+1 row inserted/updated/deleted
ij> insert into test values(1);
-ERROR 42Y03: 'MAX_VALUE' is not recognized as a function or procedure.
+1 row inserted/updated/deleted
ij> -- dropping a view which the trigger references
create table t11TriggerTest (c111 int not null primary key, c112 int);
0 rows inserted/updated/deleted
@@ -1060,21 +1057,22 @@ select * from t32TriggerTest;
C321
-----------
1
-ij> -- drop the view used by the trigger.
+ij> -- drop the view used by the trigger. should fail after DERBY-2041.
drop view v21ViewTest;
-0 rows inserted/updated/deleted
-ij> -- try an insert which would cause insert trigger to fire. The insert trigger should have failed because view doesn't
--- exist anymore.
+ERROR X0Y25: Operation 'DROP VIEW' cannot be performed on object 'V21VIEWTEST' because TRIGGER 'TR31T31TRIGGERTEST' is dependent on that object.
+ij> -- try an insert which would cause insert trigger to fire.
insert into t31TriggerTest values(1);
-ERROR 42X05: Table/View 'V21VIEWTEST' does not exist.
+1 row inserted/updated/deleted
ij> select * from t31TriggerTest;
C311
-----------
1
+1
ij> select * from t32TriggerTest;
C321
-----------
1
+1
ij> -- DERBY-630
-- NPE in CREATE TRIGGER when compilation schema is other than APP.
connect 'jdbc:derby:wombat;create=true;user=user1;password=pwd' as user1;
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?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- 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 Nov 25 08:59:24 2013
@@ -107,39 +107,39 @@ X
-----------
2
ij> -- clean up
-drop table t17;
+drop table t1;
0 rows inserted/updated/deleted
-ij> drop table t16;
+ij> drop table t2;
0 rows inserted/updated/deleted
-ij> drop table t15;
+ij> drop table t3;
0 rows inserted/updated/deleted
-ij> drop table t14;
+ij> drop table t4;
0 rows inserted/updated/deleted
-ij> drop table t13;
+ij> drop table t5;
0 rows inserted/updated/deleted
-ij> drop table t12;
+ij> drop table t6;
0 rows inserted/updated/deleted
-ij> drop table t11;
+ij> drop table t7;
0 rows inserted/updated/deleted
-ij> drop table t10;
+ij> drop table t8;
0 rows inserted/updated/deleted
ij> drop table t9;
0 rows inserted/updated/deleted
-ij> drop table t8;
+ij> drop table t10;
0 rows inserted/updated/deleted
-ij> drop table t7;
+ij> drop table t11;
0 rows inserted/updated/deleted
-ij> drop table t6;
+ij> drop table t12;
0 rows inserted/updated/deleted
-ij> drop table t5;
+ij> drop table t13;
0 rows inserted/updated/deleted
-ij> drop table t4;
+ij> drop table t14;
0 rows inserted/updated/deleted
-ij> drop table t3;
+ij> drop table t15;
0 rows inserted/updated/deleted
-ij> drop table t2;
+ij> drop table t16;
0 rows inserted/updated/deleted
-ij> drop table t1;
+ij> drop table t17;
0 rows inserted/updated/deleted
ij> -- DERBY-2195
-- Nested triggers not working properly after maximum trigger count exception is thrown
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java Mon Nov 25 08:59:24 2013
@@ -1594,41 +1594,32 @@ public final class AlterTableTest extend
st.executeUpdate(
"create trigger renc_5_tr1 after update of c2, c3, " +
"c6 on renc_4 for each row mode db2sql insert into " +
- "renc_5 (c6) values (1)");
+ "renc_5 (unq_c5, c6) values (1, 2)");
// This fails, because the tigger is dependent on it:
assertStatementError(
"X0Y25", st,
"rename column renc_4.c6 to some_name");
- // This succeeds, because the trigger is not dependent on
- // renc_5.c6. DERBY-2041 requests that triggers should be
- // marked as dependent on tables and columns in their body.
- // If that improvement is made, this test will need to be
- // changed, as the next rename would fail, and the insert
- // after it would then succeed.
-
- st.executeUpdate(
+ // This also fails because a trigger action references renc_5.c6.
+ // It didn't fail before DERBY-2041.
+ assertStatementError(
+ "X0Y25", st,
"rename column renc_5.c6 to new_name");
- // The update statement will fail, because column c6 no
- // longer exists. See DERBY-2041 for a discussion of this
- // topic.
-
st.executeUpdate(
"insert into renc_4 values(1, 2, 3, 4, 5, 6)");
- assertStatementError(
- "42X14", st, "update renc_4 set c6 = 92");
+ st.executeUpdate("update renc_4 set c6 = 92");
+ // Verify that the update caused renc_5_tr1 to fire.
rs = st.executeQuery("select * from renc_5");
-
JDBC.assertColumnNames(rs,
new String[]{"C1",
- "C2", "C3", "C4", "UNQ_C5", "NEW_NAME"
+ "C2", "C3", "C4", "UNQ_C5", "C6"
});
- JDBC.assertDrainResults(rs,
- 0);
+ JDBC.assertFullResultSet(rs,
+ new String[][] {{null, null, null, null, "1", "2"}});
// Rename a column which has a granted privilege, show
// that the grant is properly processed and now applies to
@@ -1838,7 +1829,7 @@ public final class AlterTableTest extend
int sysdependsRowCountBeforeTestStart;
sysdependsRowCountBeforeTestStart = numberOfRowsInSysdepends(st);
- //Following trigger will add 5 rows to sysdepends. Trigger creation
+ // Following trigger will add 7 rows to sysdepends. Trigger creation
// will send CREATE TRIGGER invalidation to trigger table but there
// are no other persistent dependents on trigger table at this point.
st.executeUpdate(
@@ -1847,9 +1838,10 @@ public final class AlterTableTest extend
"old_table as old for each statement insert into " +
"Derby5120_tab_bkup1 select * from old");
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+5);
+ sysdependsRowCountBeforeTestStart + 7,
+ numberOfRowsInSysdepends(st));
- //Following trigger will add 5 rows to sysdepends. Trigger creation
+ // Following trigger will add 7 rows to sysdepends. Trigger creation
// will send CREATE TRIGGER invalidation to trigger table which will
// invalidate trigger created earlier (Derby5120_tr1). Because of
// this, when Derby5120_tr1 trigger fires next, it will be recompiled.
@@ -1859,21 +1851,24 @@ public final class AlterTableTest extend
"old as oldrow for each row insert into " +
"Derby5120_tab_bkup2(c211) values (oldrow.c11)");
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+10);
+ sysdependsRowCountBeforeTestStart + 14,
+ numberOfRowsInSysdepends(st));
//Following will fire the 2 triggers created above. During the firing,
// we will find that Derby5120_tr1 has been marked invalid. As a result
// we will recompile it's trigger action.
st.executeUpdate("update Derby5120_tab set c11=2");
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+10);
+ sysdependsRowCountBeforeTestStart + 14,
+ numberOfRowsInSysdepends(st));
//Following alter table on trigger table will mark the two triggers
// created above invalid. As a result, when they are fired next
// time, their trigger action sps will be regenerated.
st.executeUpdate("alter table Derby5120_tab add column c113 int");
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+10);
+ sysdependsRowCountBeforeTestStart + 14,
+ numberOfRowsInSysdepends(st));
//Following will cause the 2 triggers to fire because they were marked
// invalid by alter table. During the trigger action sps regeneration
@@ -1887,12 +1882,14 @@ public final class AlterTableTest extend
//Drop the errorneous trigger
st.executeUpdate("drop trigger Derby5120_tr1");
Assert.assertEquals("# of rows in SYS.SYSDEPENDS will be less",
- numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+5);
+ sysdependsRowCountBeforeTestStart + 7,
+ numberOfRowsInSysdepends(st));
//Following update will succeed this time
st.executeUpdate("update Derby5120_tab set c11=2");
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+5);
+ sysdependsRowCountBeforeTestStart + 7,
+ numberOfRowsInSysdepends(st));
}
//A test for ALTER TABLE DROP COLUMN with synonyms and trigger combination.
@@ -2728,10 +2725,10 @@ public final class AlterTableTest extend
{"ATDC_13_TAB1_TRIGGER_4"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- st.executeUpdate("drop table ATDC_13_TAB1_BACKUP");
st.executeUpdate("drop table ATDC_13_TAB1");
st.executeUpdate("drop table ATDC_13_TAB2");
st.executeUpdate("drop table ATDC_13_TAB3");
+ st.executeUpdate("drop table ATDC_13_TAB1_BACKUP");
// Start of another test for DERBY-5044. Test INSERT/DELETE/UPDATE
// inside the trigger action from base tables
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DropTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DropTableTest.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DropTableTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DropTableTest.java Mon Nov 25 08:59:24 2013
@@ -210,15 +210,19 @@ public final class DropTableTest extends
" create trigger t2trig after insert on t2 for each "
+ "row insert into t1 values(1)");
- // this should work
-
- st.executeUpdate( "drop table t1");
-
- // the following should get an error when trying to
- // recompile the trigger action
-
- assertStatementError("42X05", st, "insert into t2 values(1)");
+ // this should fail because t2trig depends on t1 (used to work
+ // before DERBY-2041)
+ assertStatementError("X0Y25", st, "drop table t1");
+
+ // trigger should still work
+ st.executeUpdate("insert into t2 values(1)");
+ JDBC.assertSingleValueResultSet(
+ st.executeQuery("select * from t1"), "1");
+ JDBC.assertSingleValueResultSet(
+ st.executeQuery("select * from t2"), "1");
+
st.executeUpdate( " drop table t2");
+ st.executeUpdate( " drop table t1");
}
public void testDropTableDropView() throws SQLException{
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Mon Nov 25 08:59:24 2013
@@ -4122,7 +4122,10 @@ public final class GrantRevokeDDLTest ex
};
JDBC.assertFullResultSet(rs, expRS, true);
-
+
+ st_mamta2.executeUpdate(
+ " drop trigger tr21t21TriggerTest");
+
st_mamta1.executeUpdate(
" drop table t11TriggerTest");
@@ -4961,6 +4964,9 @@ public final class GrantRevokeDDLTest ex
JDBC.assertFullResultSet(rs, expRS, true);
st_mamta2.executeUpdate(
+ " drop trigger tr11t11");
+
+ st_mamta2.executeUpdate(
" drop table t21TriggerRevokeTest");
// set connection mamta1
@@ -5124,7 +5130,10 @@ public final class GrantRevokeDDLTest ex
};
JDBC.assertFullResultSet(rs, expRS, true);
-
+
+ st_mamta2.executeUpdate(
+ " drop trigger tr11t11");
+
st_mamta2.executeUpdate(
" drop table t21TriggerRevokeTest");
@@ -6481,7 +6490,6 @@ public final class GrantRevokeDDLTest ex
st.executeUpdate("DROP VIEW MAMTA2.V28");
st.executeUpdate("DROP VIEW MAMTA3.V21VIEWTEST");
st.executeUpdate("DROP FUNCTION MAMTA1.SELECTFROMSPECIFICSCHEMA");
- st.executeUpdate("DROP TRIGGER MAMTA2.TR21T21TRIGGERTEST");
st.executeUpdate("DROP TABLE MAMTA4.T41TRIGGERTEST");
st.executeUpdate("DROP TABLE MAMTA3.D1589T31CONSTRAINTTEST");
st.executeUpdate("DROP TABLE MAMTA3.T31");
@@ -9955,8 +9963,6 @@ public final class GrantRevokeDDLTest ex
st.executeUpdate("DROP PROCEDURE USER1.REVOKE_SELECT_PROC2");
st.executeUpdate("DROP PROCEDURE USER1.REVOKE_SELECT_PROC3");
st.executeUpdate("DROP PROCEDURE USER1.REVOKE_SELECT_PROC4");
- st.executeUpdate("DROP FUNCTION USER1.F_ABS1");
- st.executeUpdate("DROP FUNCTION USER1.F_ABS2");
st.executeUpdate("DROP TRIGGER USER2.TT1");
st.executeUpdate("DROP TRIGGER USER2.TT2");
st.executeUpdate("DROP TRIGGER USER2.TT3");
@@ -9964,6 +9970,8 @@ public final class GrantRevokeDDLTest ex
st.executeUpdate("DROP TRIGGER USER2.TT8");
st.executeUpdate("DROP TRIGGER USER5.TT9");
st.executeUpdate("DROP TRIGGER USER5.TT10");
+ st.executeUpdate("DROP FUNCTION USER1.F_ABS1");
+ st.executeUpdate("DROP FUNCTION USER1.F_ABS2");
st.executeUpdate("DROP VIEW USER4.VSHARED4");
st.executeUpdate("DROP VIEW USER4.VSHARED3");
st.executeUpdate("DROP VIEW USER4.VSHARED2");
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java Mon Nov 25 08:59:24 2013
@@ -57,12 +57,8 @@ public class ProcedureInTriggerTest exte
checkAndResetZeroArgCount(1);
ResultSet rs = s.executeQuery("SELECT * FROM T2");
JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
- //--- check that trigger firing and database event fail if the procedure referred
- //--- in the triggered sql statement is dropped
- s.execute("drop procedure proc_no_sql");
- assertStatementError("42Y03",s,"insert into t2 values (1,2), (2,4)");
- //--- after recreating the procedure, the trigger should work
- s.execute("create procedure proc_no_sql() parameter style java language java NO SQL external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.zeroArg'");
+ // Check that a procedure used by a trigger cannot be dropped.
+ assertStatementError("X0Y25", s, "drop procedure proc_no_sql");
s.execute("insert into t2 values (3,6)");
checkAndResetZeroArgCount(1);
rs = s.executeQuery("SELECT * FROM T2");
@@ -81,13 +77,8 @@ public class ProcedureInTriggerTest exte
checkAndResetZeroArgCount(1);
rs = s.executeQuery("select * from t2");
JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
- //--- check that trigger firing and database event fail if the procedure referred
- //--- in the triggered sql statement is dropped
- s.execute("drop procedure proc_no_sql");
- // --- should fail
- assertStatementError("42Y03",s,"insert into t2 values (1,2), (2,4)");
- //after recreating the procedure, the trigger should work
- s.execute("create procedure proc_no_sql() parameter style java language java NO SQL external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.zeroArg'");
+ // Check that a procedure used by a trigger cannot be dropped.
+ assertStatementError("X0Y25", s, "drop procedure proc_no_sql");
s.execute("insert into t2 values (3,6)");
checkAndResetZeroArgCount(1);
// check inserts are successful
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RenameTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RenameTableTest.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RenameTableTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RenameTableTest.java Mon Nov 25 08:59:24 2013
@@ -29,6 +29,7 @@ import java.sql.Statement;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;
/**
@@ -202,13 +203,17 @@ public class RenameTableTest extends Bas
ResultSet rs = s.executeQuery("select * from t7");
rs.next();
rs.close();
- s.executeUpdate("rename table t6 to t6r");
- assertStatementError("42X05", s, "insert into t7 values(3)");
- rs = s.executeQuery("select * from t6r");
+ // DERBY-2041: Rename of table referenced in a trigger action
+ // should fail and leave trigger intact.
+ assertStatementError("X0Y25", s, "rename table t6 to t6r");
+ s.execute("insert into t7 values(3)");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t6 order by c61"),
+ new String[][] {{"1"}, {"3"}});
assertStatementError("42X05", s, "select * from t7r");
// Clean Up
- s.executeUpdate("drop table t6r");
s.executeUpdate("drop table t7");
+ s.executeUpdate("drop table t6");
}
/**
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java Mon Nov 25 08:59:24 2013
@@ -1603,9 +1603,9 @@ public class RolesConferredPrivilegesTes
s.execute("revoke h from DonaldDuck");
s.execute("revoke insert on t from DonaldDuck");
setRole(dboConn, "none");
- s.execute("drop table trackCreds");
s.execute("drop table t");
s.execute("drop table strange");
+ s.execute("drop table trackCreds");
s.close();
c.close();
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java Mon Nov 25 08:59:24 2013
@@ -60,6 +60,11 @@ import org.apache.derbyTesting.junit.XML
*
*/
public class TriggerTest extends BaseJDBCTestCase {
+
+ private static final String SYNTAX_ERROR = "42X01";
+ private static final String HAS_DEPENDENT_SPS = "X0Y24";
+ private static final String HAS_DEPENDENT_TRIGGER = "X0Y25";
+ private static final String TRIGGER_DROPPED = "01502";
/**
* Thread local that a trigger can access to
@@ -783,7 +788,6 @@ public class TriggerTest extends BaseJDB
* Test for DERBY-3718 NPE when a trigger is fired
*
* @throws SQLException
- * @throws IOException
*/
public void testNPEinTriggerFire() throws SQLException
{
@@ -2122,4 +2126,202 @@ public class TriggerTest extends BaseJDB
// DERBY-6348.
s.execute("insert into d6348 values 1");
}
+
+ /**
+ * Test that DROP operations detect if there are triggers depending on
+ * the object being dropped, and either fail (if RESTRICT semantics) or
+ * drop the trigger (if CASCADE semantics).
+ */
+ public void testDerby2041DropDependencies() throws SQLException {
+ Statement s = createStatement();
+ s.execute("create table t1(x int, y int, z int)");
+ s.execute("create table t2(x int, y int, z int)");
+ s.execute("create table syn_table(x int, y int, z int)");
+ s.execute("create table view_table(x int, y int, z int)");
+
+ s.execute("create function f(x int) returns int language java "
+ + "parameter style java external name 'java.lang.Math.abs'");
+ s.execute("create procedure p() language java parameter style java "
+ + "external name '" + getClass().getName()
+ + ".dummyProc' no sql");
+ s.execute("create function tf() returns table (x int) "
+ + "language java parameter style derby_jdbc_result_set "
+ + "external name '" + getClass().getName()
+ + ".dummyTableFunction' no sql");
+ s.execute("create derby aggregate intmode for int external name '"
+ + ModeAggregate.class.getName() + "'");
+ s.execute("create sequence seq");
+ s.execute("create synonym syn for syn_table");
+ s.execute("create view v(x) as select x from view_table");
+ s.execute("create type tp external name 'java.util.List' language java");
+
+ s.execute("create trigger tr_t2 after insert on t1 select x from t2");
+ s.execute("create trigger tr_f after insert on t1 values f(1)");
+ s.execute("create trigger tr_p after insert on t1 call p()");
+ s.execute("create trigger tr_tf after insert on t1 "
+ + "select * from table(tf()) t");
+ s.execute("create trigger tr_intmode after insert on t1 "
+ + "select intmode(x) from (values 1,2,3) v(x)");
+ s.execute("create trigger tr_seq after insert on t1 "
+ + "values next value for seq");
+ s.execute("create trigger tr_syn after insert on t1 select * from syn");
+ s.execute("create trigger tr_v after insert on t1 select * from v");
+ s.execute("create trigger tr_tp after insert on t1 "
+ + "values cast(null as tp)");
+
+ PreparedStatement checkTrigger = prepareStatement(
+ "select triggername from sys.systriggers join sys.sysschemas "
+ + "using (schemaid) where triggername = ? and schemaname = ?");
+ checkTrigger.setString(2, getTestConfiguration().getUserName());
+
+ // DROP TABLE should fail because T2 is used in TR_T2.
+ assertStatementError(HAS_DEPENDENT_TRIGGER, s, "drop table t2");
+ checkTrigger.setString(1, "TR_T2");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(), "TR_T2");
+
+ // DROP FUNCTION should fail because F is used in TR_F.
+ assertStatementError(HAS_DEPENDENT_TRIGGER, s, "drop function f");
+ checkTrigger.setString(1, "TR_F");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(), "TR_F");
+
+ // DROP PROCEDURE should fail because P is used in TR_P.
+ assertStatementError(HAS_DEPENDENT_TRIGGER, s, "drop procedure p");
+ checkTrigger.setString(1, "TR_P");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(), "TR_P");
+
+ // DROP FUNCTION should fail because the table function TF is
+ // used in TR_TF.
+ assertStatementError(HAS_DEPENDENT_TRIGGER, s, "drop function tf");
+ checkTrigger.setString(1, "TR_TF");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(), "TR_TF");
+
+ // DROP DERBY AGGREGATE only supports RESTRICT for now.
+ assertStatementError(SYNTAX_ERROR, s,
+ "drop derby aggregate intmode cascade");
+ assertStatementError(HAS_DEPENDENT_SPS, s,
+ "drop derby aggregate intmode restrict");
+ checkTrigger.setString(1, "TR_INTMODE");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(),
+ "TR_INTMODE");
+
+ // DROP SEQUENCE only supports RESTRICT for now.
+ assertStatementError(SYNTAX_ERROR, s, "drop sequence seq cascade");
+ assertStatementError(HAS_DEPENDENT_SPS, s, "drop sequence seq restrict");
+ checkTrigger.setString(1, "TR_SEQ");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(), "TR_SEQ");
+
+ // DROP SYNONYM should fail because SYN is used in TR_SYN.
+ assertStatementError(HAS_DEPENDENT_TRIGGER, s, "drop synonym syn");
+ checkTrigger.setString(1, "TR_SYN");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(), "TR_SYN");
+
+ // DROP VIEW should fail because V is used in TR_V.
+ assertStatementError(HAS_DEPENDENT_TRIGGER, s, "drop view v");
+ checkTrigger.setString(1, "TR_V");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(), "TR_V");
+
+ // DROP TYPE only supports RESTRICT for now.
+ assertStatementError(SYNTAX_ERROR, s, "drop type tp cascade");
+ assertStatementError(HAS_DEPENDENT_SPS, s, "drop type tp restrict");
+ checkTrigger.setString(1, "TR_TP");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(), "TR_TP");
+
+ // DROP COLUMN should fail because TR_T2 uses column X.
+ assertStatementError(HAS_DEPENDENT_TRIGGER, s,
+ "alter table t2 drop column x restrict");
+ checkTrigger.setString(1, "TR_T2");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(), "TR_T2");
+
+ // DROP COLUMN should succeed in this case, since no trigger uses
+ // column Y.
+ s.execute("alter table t2 drop column y restrict");
+ assertNull(s.getWarnings());
+ checkTrigger.setString(1, "TR_T2");
+ JDBC.assertSingleValueResultSet(checkTrigger.executeQuery(), "TR_T2");
+ JDBC.assertColumnNames(s.executeQuery("select * from t2"),
+ new String[] {"X", "Z"});
+
+ // DROP COLUMN should succeed because CASCADE is specified. Should
+ // also remove the dependent trigger and produce a warning.
+ s.execute("alter table t2 drop column x cascade");
+ assertSQLState(TRIGGER_DROPPED, s.getWarnings());
+ checkTrigger.setString(1, "TR_T2");
+ JDBC.assertEmpty(checkTrigger.executeQuery());
+ JDBC.assertColumnNames(s.executeQuery("select * from t2"),
+ new String[] {"Z"});
+ }
+
+ /**
+ * Test that the fix for DERBY-2041 isn't too strict. Verify that some
+ * operations only cause the dependent triggered statement to get
+ * recompiled, and don't fail or cascade.
+ */
+ public void testDerby2041RecompileOnly() throws SQLException {
+ Statement s = createStatement();
+
+ PreparedStatement spsValid = prepareStatement("select valid from "
+ + "sys.sysschemas join sys.systriggers using (schemaid) "
+ + "join sys.sysstatements on stmtid = actionstmtid "
+ + "where schemaname = ? and triggername = ?");
+ spsValid.setString(1, getTestConfiguration().getUserName());
+ spsValid.setString(2, "TR");
+
+ // Dropping an index used by a trigger should not fail, and the
+ // trigger should not be dropped.
+ s.execute("create table t1(x int not null)");
+ s.execute("create table t2(x int not null)");
+ s.execute("create index idx on t2(x)");
+ s.execute("create trigger tr after insert on t1 "
+ + "insert into t2 values 1");
+ // SPS should be valid before index is dropped.
+ JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "true");
+ s.execute("drop index idx");
+ // SPS should be invalid after index is dropped, but the trigger
+ // should still exist and work.
+ JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "false");
+ s.execute("insert into t1 values 1");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select * from t2"), "1");
+ JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "true");
+
+ // Truncating a table referenced by a trigger should also be OK.
+ s.execute("truncate table t2");
+ assertTableRowCount("T2", 0);
+ // SPS should be invalid after truncation, but the trigger should
+ // still exist and work.
+ JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "false");
+ s.execute("insert into t1 values 1");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select * from t2"), "1");
+ JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "true");
+
+ // Now create a table T3 that has a foreign key constraint referencing
+ // T2. Create a trigger on T1 that deletes from T2. The triggered
+ // statement depends on T3 because it needs to check that the foreign
+ // key constraint is not violated when rows are deleted from T2. Since
+ // the trigger doesn't reference T3 directly, it should be possible to
+ // drop T3 and simply recompile the triggered statement. Currently,
+ // dropping the table fails because of the triggered statement's
+ // dependency.
+ s.execute("drop trigger tr");
+ s.execute("alter table t2 add constraint t2_pk primary key (x)");
+ s.execute("create table t3(x int, "
+ + "y int references t2 on delete cascade)");
+ s.execute("create trigger tr after delete on t1 delete from t2");
+ JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "true");
+ // Ideally, dropping T3 should be allowed, and the triggered
+ // statement should have been marked as not valid (needs recompile).
+ // Currently, it fails.
+ assertStatementError(HAS_DEPENDENT_TRIGGER, s, "drop table t3");
+ JDBC.assertSingleValueResultSet(spsValid.executeQuery(), "true");
+ }
+
+ /** Used as stored procedure in testDerby2041DropDependencies(). */
+ public static void dummyProc() {
+ }
+
+ /** Used as table function in testDerby2041DropDependencies(). */
+ public static ResultSet dummyTableFunction() {
+ return null;
+ }
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java Mon Nov 25 08:59:24 2013
@@ -527,16 +527,14 @@ public class TriggerWhenClauseTest exten
"alter table t2 drop column y restrict");
s.execute("alter table t2 drop column z restrict");
- // Because of DERBY-2041, dropping the whole table silently succeeds
- // and leaves the trigger around. It should have caused a warning and
- // dropped the trigger.
- s.execute("drop table t2");
+ // Dropping a table referenced in a WHEN clause should fail and leave
+ // the trigger intact. Before DERBY-2041, DROP TABLE would succeed
+ // and leave the trigger in an invalid state so that subsequent
+ // INSERT statements would fail when trying to fire the trigger.
+ assertStatementError(HAS_DEPENDENTS, s, "drop table t2");
JDBC.assertSingleValueResultSet(
s.executeQuery("select triggername from sys.systriggers"), "TR");
- // The trigger wasn't dropped, but it is now invalid and causes the
- // triggering insert to fail.
- assertStatementError(TABLE_DOES_NOT_EXIST, s,
- "insert into t1 values (1, 2, 3)");
+ s.executeUpdate("insert into t1 values (1, 2, 3)");
getConnection().rollback(sp);
// Test references to columns in both the WHEN clause and the
@@ -550,9 +548,9 @@ public class TriggerWhenClauseTest exten
"alter table t2 drop column y restrict");
s.execute("alter table t2 drop column z restrict");
- // Again, because of DERBY-2041, DROP TABLE fails to cascade and
- // drop the trigger.
- s.execute("drop table t2");
+ // DROP TABLE should fail because of the dependencies (didn't before
+ // DERBY-2041).
+ assertStatementError(HAS_DEPENDENTS, s, "drop table t2");
JDBC.assertSingleValueResultSet(
s.executeQuery("select triggername from sys.systriggers"), "TR");
getConnection().rollback(sp);
@@ -720,10 +718,10 @@ public class TriggerWhenClauseTest exten
s1.close();
s2.close();
- c1.setAutoCommit(false);
- JDBC.dropSchema(c1.getMetaData(), "U1");
c2.setAutoCommit(false);
JDBC.dropSchema(c2.getMetaData(), "U2");
+ c1.setAutoCommit(false);
+ JDBC.dropSchema(c1.getMetaData(), "U1");
}
/**
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions.sql?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions.sql Mon Nov 25 08:59:24 2013
@@ -157,10 +157,10 @@ select * from t1;
select * from t2;
select * from t3;
-drop table t4;
drop table t3;
drop table t2;
drop table t1;
+drop table t4;
--test for multiple fkeys on the same table referrring to
@@ -325,10 +325,10 @@ select * from t1;
select * from t2;
select * from t3;
-drop table t4;
drop table t3;
drop table t2;
drop table t1;
+drop table t4;
--test for multiple fkeys on the same table referrring to
@@ -393,16 +393,22 @@ insert into t2 values(2, 3);
-- parent row can not be deleted because of a dependent relationship from another table
delete from t1 where a =1;
+drop trigger trig_delete;
drop table t2;
--do the same case as above with RESTRICT
--we should get error, because RESTRICT rules are checked before firing triggers
create table t2(x int references t1(a) ON DELETE RESTRICT , y int);
+create trigger trig_delete after DELETE on t1
+referencing old as deletedrow
+for each row
+delete from t2 where x = deletedrow.a;
insert into t2 values(1, 2);
insert into t2 values(2, 3);
--following delete should throw constraint violations error
delete from t1 where a =1;
+drop trigger trig_delete;
drop table t2;
drop table t1;
@@ -428,11 +434,15 @@ update t1 set b = 7 where a =1;
select * from t1 ;
select * from t2 ;
rollback;
+drop trigger trig_update;
drop table t2;
commit;
--do the same case as above with RESTRICT
--we should get error, because RESTRICT is check before firing triggers
create table t2(x int references t1(a) ON UPDATE RESTRICT , y int);
+create trigger trig_update after UPDATE on t1
+referencing old as old for each row
+update t2 set x = 2 where x = old.a;
insert into t2 values(1, 2);
insert into t2 values(2, 3);
commit;
@@ -441,6 +451,7 @@ update t1 set a = 7 where a =1;
select * from t1 ;
select * from t2;
autocommit on;
+drop trigger trig_update;
drop table t2;
drop table t1;
@@ -468,10 +479,10 @@ select * from t1;
select * from t2;
select * from t3;
-drop table t4;
drop table t3;
drop table t2;
drop table t1;
+drop table t4;
--After Statement triggers on the dependen tables
@@ -497,10 +508,10 @@ select * from t1;
select * from t2;
select * from t3;
-drop table t4;
drop table t3;
drop table t2;
drop table t1;
+drop table t4;
--After triggers on a self referencing table
@@ -612,10 +623,10 @@ select * from t2;
select * from t1temp;
-drop table t1temp;
alter table t1 drop constraint c1;
drop table t2;
drop table t1;
+drop table t1temp;
-- triggers on a cyclic referential actions
create table t1(a int not null primary key, b int not null unique);
@@ -664,10 +675,10 @@ select * from t2;
select * from t1temp;
-drop table t1temp;
alter table t1 drop constraint c1;
drop table t2;
drop table t1;
+drop table t1temp;
--ROW triggers on a cyclic referential actions
@@ -720,10 +731,10 @@ select * from t1;
select * from t2;
select * from t1temp;
-drop table t1temp;
alter table t1 drop constraint c1;
drop table t2;
drop table t1;
+drop table t1temp;
--SET NULL UPDATE STETEMENT triggers on a self referencing table
@@ -1210,6 +1221,8 @@ select * from t1 ;
select * from t2 ;
select * from t3;
+drop trigger trig_delete;
+drop trigger trig_delete1;
drop table t3;
drop table t2;
drop table t1;
@@ -1254,6 +1267,8 @@ select * from t1 ;
select * from t2 ;
select * from t3;
+drop trigger trig_delete;
+drop trigger trig_delete1;
drop table t3;
drop table t2;
drop table t1;
@@ -1358,10 +1373,10 @@ insert into t3 values(2, 3) ;
delete from t1 where c1 = 1 ;
select * from t4 ;
-drop table t4;
drop table t3;
drop table t2;
drop table t1;
+drop table t4;
---multiple foreign keys pointing to the same table and has dependens
-- first foreign key path has zero rows qualified(bug 5197 from webshphere)
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?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql Mon Nov 25 08:59:24 2013
@@ -534,7 +534,7 @@ insert into t1 values 1;
select * from t2;
drop trigger tt;
--- dropping and recreating a table which the trigger references
+-- dropping a table which the trigger references, should fail (DERBY-2041)
create table t3 (i int);
create table t4 (i int);
create trigger tt2 after insert on t3 for each statement insert into t4 values 1;
@@ -542,8 +542,6 @@ insert into t3 values 1;
select * from t4;
drop table t4;
insert into t3 values 1;
-create table t4 (i int);
-insert into t3 values 1;
select * from t4;
-- dropping a function which the trigger references
@@ -553,7 +551,7 @@ create trigger test_trigger AFTER insert
insert into test values(1);
---- drop function and again do inserts. these should not work as the trigger would be invalid
+--- drop function should fail (DERBY-2041)
drop function max_value;
insert into test values(2);
insert into test values(1);
@@ -579,11 +577,10 @@ select * from t31TriggerTest;
-- we know the trigger got fired if there is one row in t32TriggerTest
select * from t32TriggerTest;
--- drop the view used by the trigger.
+-- drop the view used by the trigger. should fail after DERBY-2041.
drop view v21ViewTest;
--- try an insert which would cause insert trigger to fire. The insert trigger should have failed because view doesn't
--- exist anymore.
+-- try an insert which would cause insert trigger to fire.
insert into t31TriggerTest values(1);
select * from t31TriggerTest;
select * from t32TriggerTest;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRecursion.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRecursion.sql?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRecursion.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerRecursion.sql Mon Nov 25 08:59:24 2013
@@ -72,23 +72,23 @@ insert into t1 values 2;
select * from t1;
-- clean up
-drop table t17;
-drop table t16;
-drop table t15;
-drop table t14;
-drop table t13;
-drop table t12;
-drop table t11;
-drop table t10;
-drop table t9;
-drop table t8;
-drop table t7;
-drop table t6;
-drop table t5;
-drop table t4;
-drop table t3;
-drop table t2;
drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
+drop table t5;
+drop table t6;
+drop table t7;
+drop table t8;
+drop table t9;
+drop table t10;
+drop table t11;
+drop table t12;
+drop table t13;
+drop table t14;
+drop table t15;
+drop table t16;
+drop table t17;
-- DERBY-2195
-- Nested triggers not working properly after maximum trigger count exception is thrown
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java Mon Nov 25 08:59:24 2013
@@ -677,8 +677,8 @@ public class BasicSetup extends UpgradeC
Statement s = createStatement();
boolean modeDb2SqlOptional = oldAtLeast(10, 3);
- dropTable("BKUP1_5044_5120");
dropTable("TAB1_5044_5120");
+ dropTable("BKUP1_5044_5120");
s.execute("create table TAB1_5044_5120(c11 int, c12 int)");
s.execute("insert into TAB1_5044_5120 values (1,11)");
s.execute("create table BKUP1_5044_5120(c111 int, c112 int)");
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java?rev=1545179&r1=1545178&r2=1545179&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java Mon Nov 25 08:59:24 2013
@@ -300,7 +300,20 @@ public class JDBC {
Connection conn = dmd.getConnection();
Assert.assertFalse(conn.getAutoCommit());
Statement s = dmd.getConnection().createStatement();
-
+
+ // Triggers
+ PreparedStatement pstr = conn.prepareStatement(
+ "SELECT TRIGGERNAME FROM SYS.SYSSCHEMAS S, SYS.SYSTRIGGERS T "
+ + "WHERE S.SCHEMAID = T.SCHEMAID AND SCHEMANAME = ?");
+ pstr.setString(1, schema);
+ ResultSet trrs = pstr.executeQuery();
+ while (trrs.next()) {
+ String trigger = trrs.getString(1);
+ s.execute("DROP TRIGGER " + JDBC.escape(schema, trigger));
+ }
+ trrs.close();
+ pstr.close();
+
// Functions - not supported by JDBC meta data until JDBC 4
// Need to use the CHAR() function on A.ALIASTYPE
// so that the compare will work in any schema.