Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Fri, 12 Aug 2011 05:18:27 +0000 (UTC)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <12728611.32274.1313126307322.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <1883232320.19120.1297814217500.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Commented] (DERBY-5044) ALTER TABLE DROP COLUMN will not
 detect triggers defined on other tables with their trigger action using the
 column being dropped
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-5044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13083935#comment-13083935 ] 

Mamta A. Satoor commented on DERBY-5044:
----------------------------------------

Updated list of tests that I plan to add for this jira
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)Have a 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)We have test for VIEWS, but see if we need to be testing for SYNONYMS too 
6)In upgrade, test 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, test 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(we have the VIEWs variation of that test already) 
9)Add test to check how privileges might affect the trigger recompile by a user who doesn't own the trigger


> ALTER TABLE DROP COLUMN will not detect triggers defined on other tables with their trigger action using the column being dropped
> ---------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5044
>                 URL: https://issues.apache.org/jira/browse/DERBY-5044
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>              Labels: derby_triage10_8
>
> A trigger in it's trigger action.can use columns from a table other than the trigger table. When such a column is dropped, the trigger dependency does not get detected.
> A test case for this can be found in AlterTableTest.java
>         //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.
>         st.executeUpdate("create table atdc_14_tab1 (a1 integer, b1 integer)");
>         st.executeUpdate("create table atdc_14_tab2 (a2 integer, b2 integer)");        
>         st.executeUpdate("insert into atdc_14_tab1 values(1,11)");
>         st.executeUpdate("insert into atdc_14_tab2 values(1,11)");
>         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");
>         // 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");
>         rs =
>                 st.executeQuery(
>                 " select triggername from sys.systriggers where " +
>                 "triggername = 'ATDC_14_TRIGGER_1' ");
>         JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_14_TRIGGER_1"}});

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira