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 "Rick Hillegas (JIRA)" <ji...@apache.org> on 2008/11/07 15:06:44 UTC
[jira] Created: (DERBY-3940) Dropping a column does not drop
triggers which mention that 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
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.
[jira] Commented: (DERBY-3940) Dropping a column does not drop
triggers which mention that column
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12645803#action_12645803 ]
Dag H. Wanvik commented on DERBY-3940:
--------------------------------------
It seems to me the code in CreateTriggerNode#bindStatement should include all column references
in the set it constructs (referencedColInts), that would be your 1)expand meaning option.
> 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.
[jira] Updated: (DERBY-3940) Dropping a column does not drop
triggers which mention that column
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3940?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kristian Waagan updated DERBY-3940:
-----------------------------------
Issue & fix info: [Known fix, Repro attached, Workaround attached]
Urgency: Normal
Triaged July 3, 2009: Assigned normal urgency, marked as Known fix, Repro attached and Workaround attached.
> 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.1.1
> 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.
[jira] Commented: (DERBY-3940) Dropping a column does not drop
triggers which mention that column
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-3940) Dropping a column does not drop
triggers which mention that column
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3940?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3940:
---------------------------------
Attachment: dropColumnWithTrigger.sql
Triggers.java
Attaching a test case:
1) Compile Triggers.java. This class contains a procedure which will be called by the trigger.
2) Now run dropColumnWithTrigger.sql under ij.
You will see that the ALTER TABLE DROP COLUMN succeeds. However, a follow-up INSERT fails with this error:
ERROR 38000: The exception 'java.sql.SQLException: Column '3' not found.' was thrown while evaluating an expression.
ERROR S0022: Column '3' not found.
> 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.