[DERBY-3940] Dropping a column does not drop triggers which mention that column

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.4.2.1, 10.5.1.1
• Fix Version/s: 10.10.2.0, 10.11.1.1
• Component/s: SQL
• Labels: derby_backport_reject_10_8 derby_triage10_5_2
• Urgency: Normal
• Issue & fix info: Known fix, Repro attached, Workaround attached
• Assignee: Unassigned
• Reporter: Richard N. Hillegas
• Created: 07/Nov/08 14:05
• Updated: 21/Jan/15 00:23
• Resolved: 13/Sep/13 16:11

Description

Put an INSERT trigger on a table and mention a column in the trigger. Then drop that column from the table. If you drop the column with RESTRICT semantics, you don't get an objection. Both CASCADE and RESTRICT drop the column. However, the trigger remains in both cases. After that, INSERTs into the table fail because the trigger can't find the dropped column. The workaround is to manually drop the trigger either before or after dropping the column. I will attach a test case.

Issue Links

• blocks: DERBY-1482 Update triggers on tables with blob columns stream blobs into memory even when the blobs are not referenced/accessed.
• is duplicated by: DERBY-4887 ALTER TABLE DROP COLUMN leaves the dependent trigger invalid rather than drop it
• is part of: DERBY-2041 Trigger should register a dependency on tables and columns used in its body
• is related to: DERBY-1489 Provide ALTER TABLE DROP COLUMN functionality
• relates to: DERBY-481 implement SQL generated columns