[DERBY-5044] ALTER TABLE DROP COLUMN will not detect triggers defined on other tables with their trigger action using the column being dropped

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 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
• Fix Version/s: 10.8.2.2, 10.9.1.0
• Component/s: SQL
• Labels: derby_backport_reject_10_5 derby_backport_reject_10_6 derby_backport_reject_10_7 derby_triage10_8
• Urgency: Normal
• Bug behavior facts: Deviation from standard
• Assignee: Mamta A. Satoor
• Reporter: Mamta A. Satoor
• Created: 15/Feb/11 23:55
• Updated: 17/Jun/13 09:19
• Resolved: 19/Mar/12 17:05

Description

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");

Issue Links

• is required by: DERBY-5654 10.5 Backport issue (2012)