Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Mon, 19 Mar 2012 14:34:38 +0000 (UTC)
From: "Mamta A. Satoor (Commented) (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1093132736.31489.1332167678771.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=13232640#comment-13232640 ] 

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

If we do not have a customer request for backporting DERBY-5044, then I would rather us not backport this jira. The primary reason is we did work on several trigger related jiras around the same time and some of those jiras depended on changes to system tables. Those different jiras worked on quite a few common classes and around the same code and hence I am worried that we might backport something which depends on codes which should not be backported. 

I spent some time over this weekend trying to backport DERBY-5044 and found that I had to hand tweak quite a few merges for just one revision. DERBY-5044 has more than one revisions to checkin all the needed changes.

                
> 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
>             Fix For: 10.8.2.2, 10.9.0.0
>
>         Attachments: DERBY_5044_backportTo108_diff_patch1.txt, DERBY_5044_diff_patch1.txt
>
>
> 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.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira