You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mamta A. Satoor (Resolved) (JIRA)" <ji...@apache.org> on 2011/10/06 18:07:30 UTC
[jira] [Resolved] (DERBY-5044) ALTER TABLE DROP COLUMN will not
detect triggers defined on other tables with their trigger action using the
column being dropped
[ https://issues.apache.org/jira/browse/DERBY-5044?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor resolved DERBY-5044.
------------------------------------
Resolution: Fixed
> 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