db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1167226 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang: AlterTableTest.java GrantRevokeDDLTest.java
Date Fri, 09 Sep 2011 14:43:52 GMT
Author: mamta
Date: Fri Sep  9 14:43:52 2011
New Revision: 1167226

URL: http://svn.apache.org/viewvc?rev=1167226&view=rev
Log:
DERBY-5044 ALTER TABLE DROP COLUMN will not detect triggers defined on other tables with their
trigger action using the column being dropped

Adding tests for DERBY-5044. The tests included are
1)Trigger actions with INSERT sql using column being dropped(from a non-trigger table) 
2)Trigger actions with UPDATE sql using column being dropped(from a non-trigger table) 
3)Trigger actions with SELECT from VIEWS with views and trigger action using column being
dropped(from a non-trigger table) 
4)Combination of trigger types in the same test ie trigger defined on the table being altered
and trigger defined on other tables but using the table being altered in their trigger action

5)Tests using SYNONYMS
6)In upgrade, triggers who have lost one of their dependency row(DERBY-5120) and see how they
work when they are using the table who column is being dropped 
7)In upgrade, triggers using column being altered and show how it is not detected prior to
10.9 
8)Trigger action with SELECT from table using column being dropped from non-trigger table(similar
to the VIEWs variation of the test) 
9)Test to check how privileges might affect the trigger recompile by a user who doesn't own
the trigger 



Modified:
    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/GrantRevokeDDLTest.java

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=1167226&r1=1167225&r2=1167226&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
Fri Sep  9 14:43:52 2011
@@ -1790,7 +1790,7 @@ public final class AlterTableTest extend
         st.executeUpdate(
                 "rename column renc_schema_2.renc_8.b to b2");
     }
-
+    
     // DERBY-5120 Make sure that sysdepends will catch trigger
     //  table changes and cause the triggers defined on that
     //  table to recompile when they fire next time
@@ -1859,6 +1859,188 @@ public final class AlterTableTest extend
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+5);
     }
+    
+    //A test for ALTER TABLE DROP COLUMN with synonyms and trigger combination.
+    // Trigger uses synonym in it's trigger action and when a column is 
+    // dropped(in cascade mode), the trigger gets dropped because the synonym 
+    // in it's trigger action relied on that column.
+    public void testTriggersAndSynonyms() throws Exception {
+        Statement st = createStatement();
+        
+        st.executeUpdate("create table atdcSynonymTab_1 (c11 integer, c12 integer)");
+        st.executeUpdate("create table atdcSynonymTab_2 (c21 integer, c22 integer)");
+		st.executeUpdate("CREATE SYNONYM synonymTab2 FOR atdcSynonymTab_2");
+        st.executeUpdate(
+                "create trigger syn_tr1t1 after update of c11 on atdcSynonymTab_1 " +
+                "for each row mode db2sql " +
+                "insert into atdcSynonymTab_2(c21, c22) values(9,9)");
+
+        //Verify there is no data in tables before the start of the test
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdcSynonymTab_1"));
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from synonymTab2"));
+        st.executeUpdate(
+                " insert into atdcSynonymTab_1 values(11,12)");
+        //Followng will fire the trigger and insert a row in table on which
+        // there is a synonym defined
+        st.executeUpdate(
+                " update atdcSynonymTab_1 set c11=99");
+        //A new row in the table with synonym defined on it
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from synonymTab2"),
+                new String[][]{{"9","9"}});
+        //delete data to get ready for next test which will attempt to do
+        // ALTER TABLE DROP COLUMN RESTRICT and fail because there is a
+        // trigger using the column being dropped
+        st.executeUpdate(
+                " delete from atdcSynonymTab_1");
+        st.executeUpdate(
+                " delete from synonymTab2");
+        
+        //Following will fail because there is a trigger using that 
+        // column
+        assertStatementError("X0Y25", st,
+                " alter table atdcSynonymTab_2 drop column c21 restrict");
+        //Run through the trigger firing test again to see that trigger is
+        // still intact
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdcSynonymTab_1"));
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from synonymTab2"));
+        st.executeUpdate(
+                " insert into atdcSynonymTab_1 values(11,12)");
+        //Followng will fire the trigger and insert a row in table on which
+        // there is a synonym defined
+        st.executeUpdate(
+                " update atdcSynonymTab_1 set c11=99");
+        //A new row in the table with synonym defined on it
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from synonymTab2"),
+                new String[][]{{"9","9"}});
+        //delete data to get ready for next test which will attempt to do
+        // ALTER TABLE DROP COLUMN and will dropped the trigger using the 
+        // column being dropped
+        st.executeUpdate(
+                " delete from atdcSynonymTab_1");
+        st.executeUpdate(
+                " delete from synonymTab2");
+        
+        //Following will drop three triggers using the column being dropped
+        st.executeUpdate(
+                " alter table atdcSynonymTab_2 drop column c21");
+        //Run through the trigger firing test again and we will see the trigger
+        // is not there anymore since no new row gets inserted through the
+        // trigger
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdcSynonymTab_1"));
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from synonymTab2"));
+        st.executeUpdate(
+                " insert into atdcSynonymTab_1 values(11,12)");
+        st.executeUpdate(
+                " update atdcSynonymTab_1 set c11=99");
+        //Will still be empty because trigger which would have caused a row
+        // insertion got dropped as a result of ALTER TABLE DROP COLUMN
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from synonymTab2"));
+    }
+
+    // Column being dropped is getting used in two triggers. A trigger defined
+    //  on the table whose column is getting dropped and a trigger defined on
+    //  another table but using the table whose column is getting dropped in
+    //  it's trigger action
+    public void testDropColumnTriggerDependency() throws Exception {
+        Statement st = createStatement();
+        createTestObjects(st);
+
+        st.executeUpdate("create table atdctd_1 (c11 integer, c12 integer)");
+        st.executeUpdate("create table atdctd_2 (c21 integer, c22 integer)");
+        st.executeUpdate("create table atdctd_3 (c31 integer, c32 integer)");
+
+        st.executeUpdate(
+                "create trigger tr1t1 after update of c11 on atdctd_1 " +
+                "for each row mode db2sql " +
+                "insert into atdctd_3(c31, c32) values(9,9)");
+
+        st.executeUpdate(
+                "create trigger tr1t2 after insert on atdctd_2 " +
+                "for each row mode db2sql " +
+                "insert into atdctd_3(c31, c32) " +
+                "select c11, c12 from atdctd_1");
+        st.executeUpdate(
+                "create trigger tr2t2 after insert on atdctd_2 " +
+                "for each row mode db2sql " +
+                "insert into atdctd_3(c31) " +
+                "select c11 from atdctd_1");
+
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdctd_3"));
+        st.executeUpdate(
+                " insert into atdctd_1 values(11,12)");
+        st.executeUpdate(
+                " update atdctd_1 set c11=99");
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from atdctd_3"),
+                new String[][]{{"9","9"}});
+        st.executeUpdate(
+                " insert into atdctd_2 values(21,22)");
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from atdctd_3 order by c32"),
+                new String[][]{{"9","9"}, {"99","12"},{"99",null}});
+        st.executeUpdate(
+                " delete from atdctd_3");
+        st.executeUpdate(
+                " delete from atdctd_1");
+        st.executeUpdate(
+                " delete from atdctd_2");
+        
+        //Following will fail because there are three triggers using that 
+        // column
+        assertStatementError("X0Y25", st,
+                " alter table atdctd_1 drop column c11 restrict");
+        JDBC.assertEmpty(st.executeQuery(
+        		" select * from atdctd_3"));
+        st.executeUpdate(
+        		" insert into atdctd_1 values(11,12)");
+        st.executeUpdate(
+        		" update atdctd_1 set c11=99");
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from atdctd_3"),
+                new String[][]{{"9","9"}});
+        st.executeUpdate(
+                " insert into atdctd_2 values(21,22)");
+        JDBC.assertFullResultSet(
+        		st.executeQuery("select * from atdctd_3 order by c32"),
+                new String[][]{{"9","9"}, {"99","12"},{"99",null}});
+        st.executeUpdate(
+                " delete from atdctd_3");
+        st.executeUpdate(
+                " delete from atdctd_1");
+        st.executeUpdate(
+                " delete from atdctd_2");
+        
+        //Following will drop three triggers using the column being dropped
+        st.executeUpdate(
+                " alter table atdctd_1 drop column c11");
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdctd_3"));
+        st.executeUpdate(
+                " insert into atdctd_1 values(12)");
+        st.executeUpdate(
+                " update atdctd_1 set c12=99");
+        //Will still be empty because trigger which would have added a row into
+        // atdctd_3 got dropped as a result of ALTER TABLE DROP COLUMN earlier
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdctd_3"));
+        st.executeUpdate(
+                " insert into atdctd_2 values(21,22)");
+        //Will still be empty because triggers which would have added a row 
+        // each into atdctd_3 got dropped as a result of ALTER TABLE DROP 
+        // COLUMN earlier
+        JDBC.assertEmpty(st.executeQuery(
+                " select * from atdctd_3"));
+    }
 
     // alter table tests for ALTER TABLE DROP COLUMN. The 
     // overall syntax is:    ALTER TABLE tablename DROP [ 
@@ -2475,8 +2657,7 @@ public final class AlterTableTest extend
         // Another test DERBY-5044
         //Following test case involves two tables. The trigger is defined 
         //on table 1 and it uses the column from table 2 in it's trigger  
-    	//action. This dependency of the trigger on a column from another 
-        //table is not detected by Derby.
+    	//action. 
         createTableAndInsertData(st, "ATDC_14_TAB1", "A1", "B1");
         createTableAndInsertData(st, "ATDC_14_TAB2", "A2", "B2");
         sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
@@ -2487,42 +2668,73 @@ public final class AlterTableTest extend
                 "update atdc_14_tab2 set a2 = newt.a1");
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
 
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
-        st.executeUpdate("alter table atdc_14_tab2 drop column a2 restrict");
+        assertStatementError("X0Y25", st,
+		"alter table atdc_14_tab2 drop column a2 restrict");
         triggersExist(st, new String[][]{{"ATDC_14_TRIGGER_1"}});
-        // following is wrong. 
-        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
 
         //Now try ALTER TABLE DROP COLUMN CASCADE where the column being
         //dropped is in trigger action of trigger defined on a different table
-        st.executeUpdate("drop trigger atdc_14_trigger_1");
-        st.executeUpdate("drop table atdc_14_tab1");
-        st.executeUpdate("drop table atdc_14_tab2");
-        createTableAndInsertData(st, "ATDC_14_TAB1", "A1", "B1");
-        createTableAndInsertData(st, "ATDC_14_TAB2", "A2", "B2");
+        st.executeUpdate("alter table atdc_14_tab2 drop column a2");
+        checkWarning(st, "01502");
+        // the trigger will get dropped as a result of cascade
+        JDBC.assertEmpty(st.executeQuery(
+        		" select triggername from sys.systriggers where " +
+        		"triggername in ('ATDC_14_TRIGGER_1')"));
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
+        st.executeUpdate("drop table ATDC_14_TAB1");
+        st.executeUpdate("drop table ATDC_14_TAB2");
+
+        // Start of another test for DERBY-5044
+        createTableAndInsertData(st, "ATDC_13_TAB1", "C11", "C12");
+        createTableAndInsertData(st, "ATDC_13_TAB1_BACKUP", "C11", "C12");
+        createTableAndInsertData(st, "ATDC_13_TAB2", "C21", "C22");
+        createTableAndInsertData(st, "ATDC_13_TAB3", "C31", "C32");
         sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
         st.executeUpdate(
-                " create trigger atdc_14_trigger_1 after update " +
-                "on atdc_14_tab1 REFERENCING NEW AS newt " +
-                "for each row " +
-                "update atdc_14_tab2 set a2 = newt.a1");
+                " create trigger ATDC_13_TAB1_trigger_1 after update " +
+                "on ATDC_13_TAB1 for each row " +
+                "INSERT INTO ATDC_13_TAB1_BACKUP " +
+                " SELECT C31, C32 from ATDC_13_TAB3");
+        st.executeUpdate(
+                " create trigger ATDC_13_TAB1_trigger_2 after update " +
+                "on ATDC_13_TAB1 for each row " +
+                "INSERT INTO ATDC_13_TAB1_BACKUP " +
+                " SELECT * from ATDC_13_TAB3");
+        countAfter2Triggers = numberOfRowsInSysdepends(st);
+        st.executeUpdate(
+                " create trigger ATDC_13_TAB1_trigger_3 after update " +
+                "on ATDC_13_TAB1 for each row " +
+                "INSERT INTO ATDC_13_TAB1_BACKUP VALUES(1,1)");
+        int countAfter3rdTrigger = numberOfRowsInSysdepends(st);
+        st.executeUpdate(
+                " create trigger ATDC_13_TAB1_trigger_4 after update " +
+                "on ATDC_13_TAB1 for each row " +
+                "INSERT INTO ATDC_13_TAB1_BACKUP(C11) " +
+                " SELECT C21 from ATDC_13_TAB2");
+        int countAfter4thTrigger = numberOfRowsInSysdepends(st);
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        st.executeUpdate("update ATDC_13_TAB1 set c12=11");
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        rs = st.executeQuery("select * from ATDC_13_TAB1_BACKUP ORDER BY C11, C12");
+        JDBC.assertFullResultSet(rs, new String[][]{
+        		{"1","1"}, {"1","11"}, {"1","11"}, {"1","11"}, {"1",null} });
+        st.executeUpdate("delete from ATDC_13_TAB1_BACKUP");
 
-        // following is not the right behavior. we should have dropped 
-        // trigger ATDC_14_TRIGGER_1 because of DROP COLUMN CASCADE
-        st.executeUpdate("alter table atdc_14_tab2 drop column a2");
-        rs =
-                st.executeQuery(
-                " select triggername from sys.systriggers where " +
-                "triggername = 'ATDC_14_TRIGGER_1' ");
-        JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_14_TRIGGER_1"}});
-        // following is wrong. 
+        assertStatementError("X0Y25", st,
+		"alter table ATDC_13_TAB2 drop column c21 restrict");
+        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
+            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
+            	{"ATDC_13_TAB1_TRIGGER_4"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        st.executeUpdate("drop table ATDC_14_TAB1");
-        st.executeUpdate("drop table ATDC_14_TAB2");
+        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");
         
         // Start of another test for DERBY-5044. Test INSERT/DELETE/UPDATE
         // inside the trigger action from base tables
@@ -2556,67 +2768,46 @@ public final class AlterTableTest extend
                 " SELECT C21 from ATDC_13_TAB2");
         countAfter4Triggers = numberOfRowsInSysdepends(st);
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
-/*
-        DERBY-5120
-        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
         st.executeUpdate("update ATDC_13_TAB1 set c12=11");
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
         rs = st.executeQuery("select * from ATDC_13_TAB1_BACKUP ORDER BY C11, C12");
-        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
         JDBC.assertFullResultSet(rs, new String[][]{
         		{"1","1"}, {"1","11"}, {"1","11"}, {"1","11"}, {"1",null} });
-        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
         st.executeUpdate("delete from ATDC_13_TAB1_BACKUP");
-*/
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
-        st.executeUpdate("alter table ATDC_13_TAB2 drop column c21 restrict");
+        //We will get an error because column being dropped is getting used 
+        // in a trigger action 
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB2 drop column c21 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
             	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
             	{"ATDC_13_TAB1_TRIGGER_4"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent triggers  
         st.executeUpdate("alter table ATDC_13_TAB2 drop column c21");
+        checkWarning(st, "01502");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
-            	{"ATDC_13_TAB1_TRIGGER_4"}});
+            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        		numberOfRowsInSysdepends(st),countAfter3Triggers);
         st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
         
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c11 restrict");
+        //We will get an error because column being dropped is getting used 
+        // in a trigger action 
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB1_BACKUP drop column c11 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
-            	{"ATDC_13_TAB1_TRIGGER_4"}});
+            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c11 int");
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
+        		numberOfRowsInSysdepends(st),countAfter3Triggers);
+        
+        // We will drop the dependent triggers  
         st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c11");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
-            	{"ATDC_13_TAB1_TRIGGER_4"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
         st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c11 int");
-        //Done testing triggers with trigger action doing INSERT
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_2");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_3");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_4");
         
         //Test triggers with trigger action doing UPDATE
         sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
@@ -2639,75 +2830,47 @@ public final class AlterTableTest extend
         countAfter3Triggers = numberOfRowsInSysdepends(st);
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
 
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
-        st.executeUpdate("alter table ATDC_13_TAB3 drop column c31 restrict");
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB3 drop column c31 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
             	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB3 add column c31 int");
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent trigger
         st.executeUpdate("alter table ATDC_13_TAB3 drop column c31");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter2Triggers
+            	{"ATDC_13_TAB1_TRIGGER_2"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        		numberOfRowsInSysdepends(st),countAfter2Triggers);
         // After DERBY-5044 is fixed, following should be rewritten
         st.executeUpdate("alter table ATDC_13_TAB3 add column c31 int");
 
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
-        st.executeUpdate("alter table ATDC_13_TAB2 drop column c21 restrict");
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB2 drop column c21 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+            	{"ATDC_13_TAB1_TRIGGER_2"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
+        		numberOfRowsInSysdepends(st),countAfter2Triggers);
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent trigger
         st.executeUpdate("alter table ATDC_13_TAB2 drop column c21");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter1Trigger
+        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following should be rewritten
-        st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
+        		numberOfRowsInSysdepends(st),countAfter1Trigger);
 
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c12 restrict");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB1_BACKUP drop column c12 restrict");
+        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
+        		numberOfRowsInSysdepends(st),countAfter1Trigger);
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent trigger
         st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c12");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter1Trigger
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
         // After DERBY-5044 is fixed, following should be rewritten
         st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
-        //Done testing triggers with trigger action doing UPDATE
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_2");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_3");
 
         //Test triggers with trigger action doing DELETE
         sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
@@ -2725,54 +2888,29 @@ public final class AlterTableTest extend
         countAfter2Triggers = numberOfRowsInSysdepends(st);
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
 
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
-        st.executeUpdate("alter table ATDC_13_TAB3 drop column c32 restrict");
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB3 drop column c32 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
             	{"ATDC_13_TAB1_TRIGGER_2"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB3 add column c32 int");
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent trigger
         st.executeUpdate("alter table ATDC_13_TAB3 drop column c32");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}});
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter1Trigger
+        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following should be rewritten
-        st.executeUpdate("alter table ATDC_13_TAB3 add column c32 int");
+        		numberOfRowsInSysdepends(st),countAfter1Trigger);
 
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c12 restrict");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}});
+        assertStatementError("X0Y25", st,
+        		"alter table ATDC_13_TAB1_BACKUP drop column c12 restrict");
+        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following won't be needed
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
+        		numberOfRowsInSysdepends(st),countAfter1Trigger);
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers  
+        // We will drop the dependent trigger
         st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c12");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}});
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter1Trigger
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following should be rewritten
-        st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
-
-        //Done testing triggers with trigger action doing DELETE
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_2");
-
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
         st.executeUpdate("drop table ATDC_13_TAB1");
         st.executeUpdate("drop table ATDC_13_TAB1_BACKUP");
         st.executeUpdate("drop table ATDC_13_TAB2");
@@ -2786,12 +2924,14 @@ public final class AlterTableTest extend
         createTableAndInsertData(st, "ATDC_13_TAB1", "C11", "C12");
         createTableAndInsertData(st, "ATDC_13_TAB2", "C11", "C12");
         createTableAndInsertData(st, "ATDC_13_TAB3", "C11", "C12");
+        
         st.executeUpdate("create view ATDC_13_VIEW1 as " +
         		"select c11 from ATDC_13_TAB2");
+        st.executeUpdate("create view ATDC_13_VIEW3 as " +
+        		"select * from ATDC_13_TAB2");
         st.executeUpdate("create view ATDC_13_VIEW2 as " +
         		"select c12 from ATDC_13_TAB3 where c12>0");
-        st.executeUpdate("create view ATDC_13_VIEW3 as " +
-		"select * from ATDC_13_TAB2");
+        
         //Test triggers with trigger action using views
         sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
         st.executeUpdate(
@@ -2802,60 +2942,49 @@ public final class AlterTableTest extend
         st.executeUpdate(
                 " create trigger ATDC_13_TAB1_trigger_2 after update " +
                 "on ATDC_13_TAB1 for each row " +
-                "SELECT * from ATDC_13_VIEW2 ");
+                "SELECT * from ATDC_13_VIEW3");
         countAfter2Triggers = numberOfRowsInSysdepends(st);
         st.executeUpdate(
                 " create trigger ATDC_13_TAB1_trigger_3 after update " +
                 "on ATDC_13_TAB1 for each row " +
-                "SELECT * from ATDC_13_VIEW3");
+                "SELECT * from ATDC_13_VIEW2 ");
         countAfter3Triggers = numberOfRowsInSysdepends(st);
         sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
 
         // DROP COLUMN RESTRICT fails because there is a view using the column
         assertStatementError("X0Y23", st,
-		"alter table ATDC_13_TAB3 drop column c12 restrict");
+        		"alter table ATDC_13_TAB3 drop column c12 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
             	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
         		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers while dropping dependent view
         st.executeUpdate("alter table ATDC_13_TAB3 drop column c12");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+            	{"ATDC_13_TAB1_TRIGGER_2"}});
         // One row from sysdepends got dropped because of a view getting
-        // dropped
-        sysdependsRowCountAfterCreateTrigger = sysdependsRowCountAfterCreateTrigger-1;
+        // dropped and that is why we are checking for countAfter2Triggers-1
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following should be rewritten
-        st.executeUpdate("alter table ATDC_13_TAB3 add column c32 int");
+        		numberOfRowsInSysdepends(st),countAfter2Triggers-1);
 
-        // DROP COLUMN RESTRICT fails because there is a view using the column
+        // DROP COLUMN RESTRICT fails as there are 2 views using the column
         assertStatementError("X0Y23", st,
 		"alter table ATDC_13_TAB2 drop column c11 restrict");
         triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+            	{"ATDC_13_TAB1_TRIGGER_2"}});
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+        		numberOfRowsInSysdepends(st),countAfter2Triggers-1);
         
-        // following is not the right behavior. we should have dropped 
-        // dependent triggers while dropping dependent view
+        // We have dropped dependent triggers while dropping dependent view
         st.executeUpdate("alter table ATDC_13_TAB2 drop column c11");
-        triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
-            	{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
         // Two rows from sysdepends got dropped because of 2 views getting
-        // dropped
-        sysdependsRowCountAfterCreateTrigger = sysdependsRowCountAfterCreateTrigger-2;
-        // DERBY-5044 The row count in systriggers should  have been 
-        // countAfter1Trigger
+        // dropped from the drop column c11 from ATDC_13_TAB2. Additionally,
+        // another view was dropped from drop of c12 from ATDC_13_TAB3.
+        // So 3 dependencies altogether got lost from sysdepends in
+        // addition to the dependencies that triggers had required.
         Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
-        		numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
-        // After DERBY-5044 is fixed, following should be rewritten
-        st.executeUpdate("alter table ATDC_13_TAB2 add column c11 int");
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger-3);
 
-        st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
         st.executeUpdate("drop table ATDC_13_TAB1");
         st.executeUpdate("drop table ATDC_13_TAB2");
         st.executeUpdate("drop table ATDC_13_TAB3");

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=1167226&r1=1167225&r2=1167226&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
Fri Sep  9 14:43:52 2011
@@ -10361,5 +10361,206 @@ public final class GrantRevokeDDLTest ex
         st_mamta1.execute("drop view v_4502");
         st_mamta1.execute("drop schema mamta1 restrict");
     }
+
+    // DERBY-5044 During alter table drop column, we recompile all the 
+    //  dependent trigger's action plans to see if they are dependent
+    //  on the column being dropped. The database may have been created
+    //  with authorization on and hence different actions might require
+    //  relevant privileges. This test will ensure that during the
+    //  recompile of trigger action, we will not loose the privilege
+    //  requirements for the triggers
+    public void testAlterTablePrivilegesIntace() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        
+        Connection user1Connection = openUserConnection("user1");
+        Statement st_user1Connection = user1Connection.createStatement();
+        
+        st = createStatement();
+        
+        st_user1Connection.executeUpdate(
+        		"create table user1.t11 (c111 int, c112 int, c113 int)");
+        st_user1Connection.executeUpdate(
+        		"create table user1.t12 (c121 int, c122 int)");
+        st_user1Connection.executeUpdate(
+        		"create table user1.t13 (c131 int, c132 int)");        
+        st_user1Connection.executeUpdate(
+                " insert into user1.t11 values(1,2,3)");
+        st_user1Connection.executeUpdate(
+                " grant trigger on user1.t12 to user2");
+        st_user1Connection.executeUpdate(
+                " grant update(c112, c113) on user1.t11 to user2");
+        st_user1Connection.executeUpdate(
+                " grant select on user1.t11 to user2");
+        st_user1Connection.executeUpdate(
+                " grant insert on user1.t13 to user2");
+
+        Connection user2Connection = openUserConnection("user2");
+        Statement st_user2Connection = user2Connection.createStatement();
+        st_user2Connection.executeUpdate(
+                "create trigger tr1t12 after insert on user1.t12 " +
+                "for each row mode db2sql " +
+                "update user1.t11 set c112=222");
+        st_user2Connection.executeUpdate(
+                "create trigger tr2t12 after insert on user1.t12 " +
+                "for each row mode db2sql " +
+                "insert into user1.t13(c131, c132) " +
+                "select c111, c113 from user1.t11");
+
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","2","3"}});
+        JDBC.assertEmpty(st_user1Connection.executeQuery(
+                " select * from user1.t13"));
+		st_user1Connection.executeUpdate(" insert into user1.t12 values(91,91)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","222","3"}});
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t13"),
+                new String[][]{{"1","3"}});
+        st_user1Connection.executeUpdate(
+                "delete from user1.t11");        
+        st_user1Connection.executeUpdate(
+                "delete from user1.t13");        
+        st_user1Connection.executeUpdate(
+                " insert into user1.t11 values(1,2,3)");
+  
+        assertStatementError("X0Y25", st_user1Connection,
+                "alter table t11 drop column c112 restrict");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","2","3"}});
+        JDBC.assertEmpty(st_user1Connection.executeQuery(
+                " select * from user1.t13"));
+		st_user1Connection.executeUpdate(" insert into user1.t12 values(92,92)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","222","3"}});
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t13"),
+                new String[][]{{"1","3"}});
+        st_user1Connection.executeUpdate(
+                "delete from user1.t11");        
+        st_user1Connection.executeUpdate(
+                "delete from user1.t13");        
+        st_user1Connection.executeUpdate(
+                " insert into user1.t11 values(1,2,3)");
+        
+        st_user1Connection.executeUpdate("alter table t11 drop column c112");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","3"}});
+        JDBC.assertEmpty(st_user1Connection.executeQuery(
+                " select * from user1.t13"));
+		st_user1Connection.executeUpdate(" insert into user1.t12 values(93,93)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","3"}});
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t13"),
+                new String[][]{{"1","3"}});
+        st_user1Connection.executeUpdate(
+                "delete from user1.t11");        
+        st_user1Connection.executeUpdate(
+                "delete from user1.t13");        
+        st_user1Connection.executeUpdate(
+                " insert into user1.t11 values(1,3)");
+        
+        st_user1Connection.executeUpdate(
+        		"revoke insert on table user1.t13 from user2");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","3"}});
+        JDBC.assertEmpty(st_user1Connection.executeQuery(
+                " select * from user1.t13"));
+		st_user1Connection.executeUpdate(" insert into user1.t12 values(94,94)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{"1","3"}});
+        JDBC.assertEmpty(st_user1Connection.executeQuery(
+        		" select * from user1.t13"));
+        st_user1Connection.executeUpdate(
+        		"drop table user1.t11");
+        st_user1Connection.executeUpdate(
+        		"drop table user1.t12");
+        st_user1Connection.executeUpdate(
+        		"drop table user1.t13");
+    }
+
+    // DERBY-5044 During alter table drop column, we recompile all the 
+    //  dependent trigger's action plans to see if they are dependent
+    //  on the column being dropped. Some of these triggers may have
+    //  been created by a user different than one doing the alter table.
+    //  The test below shows that we are able to handle such a case
+    //  and able to detect trigger dependencies even if they are created
+    //  by a different user
+    public void testAlterTableWithPrivileges() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        
+        Connection user1Connection = openUserConnection("user1");
+        Statement st_user1Connection = user1Connection.createStatement();
+        
+        st = createStatement();
+        
+        st_user1Connection.executeUpdate(
+        		"create table user1.t11 (c111 int, c112 int)");
+        st_user1Connection.executeUpdate(
+        		"create table user1.t12 (c121 int, c122 int)");
+        
+        Connection user2Connection = openUserConnection("user2");
+        Statement st_user2Connection = user2Connection.createStatement();
+  
+        // following create trigger fails because it is getting created on 
+        //  non-granted object
+        assertStatementError("42500", st_user2Connection,
+            "create trigger tr1t12 after insert on user1.t12 for each row " +
+            "mode db2sql insert into user1.t11(c112) values (1)");
+        
+        st_user1Connection.executeUpdate(
+        		" grant insert on user1.t11 to user2");
+        st_user1Connection.executeUpdate(
+        		" grant trigger on user1.t12 to user2");
+        
+        // following create trigger should pass because user2 now has necessary
+        //  privileges
+        st_user2Connection.executeUpdate(
+                "create trigger tr1t12 after insert on user1.t12 " +
+                "for each row mode db2sql " +
+                "insert into user1.t11(c112) values (1)");
+        
+        st_user1Connection.executeUpdate(
+                " insert into user1.t12 values(91,91)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{null, "1"}});
+        
+        // following should fail because there is a dependent trigger on 
+        //  t11.c112 and drop column is getting done in restrict mode
+        assertStatementError("X0Y25", st_user1Connection,
+                "alter table t11 drop column c112 restrict");
+        st_user1Connection.executeUpdate(
+                " insert into user1.t12 values(92,92)");
+        JDBC.assertFullResultSet(
+                st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{null, "1"}, {null,"1"}});
+        // following should pass because drop column is getting done in 
+        //  cascade mode and so the dependent trigger will be dropped
+        st_user1Connection.executeUpdate(
+                "alter table t11 drop column c112");        
+        //No new row will be inserted into user1.t11 because the trigger has
+        //  been dropped
+        st_user1Connection.executeUpdate(
+                " insert into user1.t12 values(93,93)");
+        JDBC.assertFullResultSet(
+        		st_user1Connection.executeQuery(" select * from user1.t11"),
+                new String[][]{{null}, {null}});
+        st_user1Connection.executeUpdate(
+                "drop table user1.t11");
+        st_user1Connection.executeUpdate(
+                "drop table user1.t12");
+    }
+
     
 }



Mime
View raw message