[
https://issues.apache.org/jira/browse/DERBY-3940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12645788#action_12645788
]
Rick Hillegas commented on DERBY-3940:
--------------------------------------
Here's my quick analysis of the problem.
AlterTableConstantAction.dropColumnFromTable() holds the logic to detect if
there are any triggers which depend on the column being dropped. That routine
checks whether the dropped column is in the array in
TriggerDescriptor.getReferencedCols() for any trigger. That, in turn,
translates into whether the dropped column turns up in the ReferencedColumns
object for some trigger. That object is persisted in
SYS.SYSTRIGGERS.REFERENCEDCOLUMNS. Unfortunately, the only columns which are
recorded in that object are the columns which appear in the UPDATE OF clause of
the trigger. We don't persistently record the columns which appear in the
action clause of the trigger. Here are some possible solutions to this problem:
1) Persistently record the columns which appear in the action clauses of
triggers. This could involve expanding the meaning of the REFERENCEDCOLUMNS
column or adding a new column to SYS.SYSTRIGGERS. In order to handle
pre-existing triggers, the upgrade logic would need to drop and recreate all
triggers.
2) Alternatively, AlterTableConstantAction.dropColumnFromTable() could parse
the action statements of all triggers on the table to see if any of them
mention the dropped column.
> Dropping a column does not drop triggers which mention that column
> ------------------------------------------------------------------
>
> Key: DERBY-3940
> URL: https://issues.apache.org/jira/browse/DERBY-3940
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.2.1, 10.5.0.0
> Reporter: Rick Hillegas
> Attachments: dropColumnWithTrigger.sql, Triggers.java
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
