[jira] Updated: (DERBY-3940) Dropping a column does not drop triggers which mention that column

["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.