[ https://issues.apache.org/jira/browse/DERBY-5044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13083925#comment-13083925 ]
Dag H. Wanvik commented on DERBY-5044: -------------------------------------- That's good news, Mamta! Thanks! > 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