Derby
  1. Derby
  2. DERBY-3940

Dropping a column does not drop triggers which mention that column

    Details

    • Urgency:
      Normal
    • Issue & fix info:
      Known fix, Repro attached, Workaround attached

      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.

      1. Triggers.java
        2 kB
        Rick Hillegas
      2. dropColumnWithTrigger.sql
        2 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          When this bug is fixed, we will need to make sure that our solution works for triggers on generated columns whose generation clauses mention the column that is being dropped.

          Show
          Rick Hillegas added a comment - When this bug is fixed, we will need to make sure that our solution works for triggers on generated columns whose generation clauses mention the column that is being dropped.
          Hide
          Dag H. Wanvik added a comment -

          Good find, Rick. Linking to DERBY-1489.

          Show
          Dag H. Wanvik added a comment - Good find, Rick. Linking to DERBY-1489 .
          Hide
          Dag H. Wanvik added a comment -

          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.

          Show
          Dag H. Wanvik added a comment - 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.
          Hide
          Kristian Waagan added a comment -

          Triaged July 3, 2009: Assigned normal urgency, marked as Known fix, Repro attached and Workaround attached.

          Show
          Kristian Waagan added a comment - Triaged July 3, 2009: Assigned normal urgency, marked as Known fix, Repro attached and Workaround attached.
          Hide
          Rick Hillegas added a comment -

          The fix for DERBY-1482 may have provided enough information to find these dangling triggers at DROP COLUMN time.

          Show
          Rick Hillegas added a comment - The fix for DERBY-1482 may have provided enough information to find these dangling triggers at DROP COLUMN time.
          Hide
          Knut Anders Hatlen added a comment -

          DERBY-2041 is a generalization of this issue, as it mentions both the DROP COLUMN case and DROP TABLE. Would it make sense to resolve this issue as a duplicate of DERBY-2041, or is it worth tracking the DROP COLUMN case separately here?

          Show
          Knut Anders Hatlen added a comment - DERBY-2041 is a generalization of this issue, as it mentions both the DROP COLUMN case and DROP TABLE. Would it make sense to resolve this issue as a duplicate of DERBY-2041 , or is it worth tracking the DROP COLUMN case separately here?
          Hide
          Knut Anders Hatlen added a comment -

          By the way, I ran the repro with 10.10.1.1 and noticed that it no longer behaved as described. This had changed:

          • ALTER TABLE DROP COLUMN still succeeded, but now with the following warning: WARNING 01502: The trigger T_DC_6_PRIM_TRIG_AFTER_INSERT_ROW_TRIGGER on table T_DC_6_PRIM has been dropped.
          • The trigger actually got dropped, so the follow-up INSERT no longer failed.

          Does this mean the bug reported here is fixed and the issue can be closed?

          Show
          Knut Anders Hatlen added a comment - By the way, I ran the repro with 10.10.1.1 and noticed that it no longer behaved as described. This had changed: ALTER TABLE DROP COLUMN still succeeded, but now with the following warning: WARNING 01502: The trigger T_DC_6_PRIM_TRIG_AFTER_INSERT_ROW_TRIGGER on table T_DC_6_PRIM has been dropped. The trigger actually got dropped, so the follow-up INSERT no longer failed. Does this mean the bug reported here is fixed and the issue can be closed?
          Hide
          Rick Hillegas added a comment -

          Thanks for verifying that the bug was fixed in 10.10.1.1. I've verified that the trigger is also dropped by the ALTER TABLE DROP COLUMN command on the 10.11 trunk. Resolving this issue to note that it is fixed in the trunk and at the head of the 10.10 branch.

          Show
          Rick Hillegas added a comment - Thanks for verifying that the bug was fixed in 10.10.1.1. I've verified that the trigger is also dropped by the ALTER TABLE DROP COLUMN command on the 10.11 trunk. Resolving this issue to note that it is fixed in the trunk and at the head of the 10.10 branch.
          Hide
          Myrna van Lunteren added a comment -

          Because we don't actually have a change for this to backport, I'm marking it as derby_reject_backport_10.8 so it doesn't show up in my query.

          Show
          Myrna van Lunteren added a comment - Because we don't actually have a change for this to backport, I'm marking it as derby_reject_backport_10.8 so it doesn't show up in my query.
          Hide
          Myrna van Lunteren added a comment -

          bulk change to close all issues resolved but not closed and not changed since June 1, 2014.

          Show
          Myrna van Lunteren added a comment - bulk change to close all issues resolved but not closed and not changed since June 1, 2014.

            People

            • Assignee:
              Unassigned
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development