Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4887

ALTER TABLE DROP COLUMN leaves the dependent trigger invalid rather than drop it

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard

      Description

      If a trigger references a column, it seems like ALTER TABLE DROP COLUMN should either
      (a) refuse to run, because the trigger is referencing that column, or
      (b) drop the column, and also drop the trigger .

      Leaving the trigger in the system, referencing a non-existent column, does not seem like desirable behavior.

      It seems like the "CASCADE" and "RESTRICT" forms of DROP COLUMN should control whether case (a) or (b) is taken by the ALTER TABLE.

      Currently, ALTER TABLE DROP COLUMN leaves the stored prepared statement for trigger action invalid. The trigger action relies on the column positions of the columns but those positoins are not valid anymore after ALTER TABLE DROP COLUMN. In worst case scenario, the trigger action ends up using data for invalid columns. eg of that is as follows

      connect 'jdbc:derby:wombat;create=true';
      – Create the table
      CREATE TABLE tab (
      element_id INTEGER NOT NULL,
      altered_id VARCHAR(30) NOT NULL,
      counter SMALLINT NOT NULL DEFAULT 0,
      timets TIMESTAMP NOT NULL
      );
      – Create a trigger against the table
      CREATE TRIGGER mytrig
      AFTER UPDATE ON tab
      REFERENCING NEW AS newt OLD AS oldt
      FOR EACH ROW MODE DB2SQL
      UPDATE tab set tab.counter = CASE WHEN (oldt.counter < 32767) THEN (oldt.counter + 1) ELSE 1 END
      WHERE ((newt.counter is null) or (oldt.counter = newt.counter))
      AND newt.element_id = tab.element_id
      AND newt.altered_id = tab.altered_id;

      – Next, we dop and recreate the column (with a different length) and a row into the table
      alter table tab drop column altered_id;
      ALTER TABLE TAB ADD COLUMN altered_id VARCHAR(64);
      insert into tab(element_id, altered_id, counter, timets) values (99, '1234567890',1,CURRENT_TIMESTAMP);
      select * from tab;
      ELEMENT_ID |COUNT&|TIMETS |ALTERED_ID
      ----------------------------------------------------------------------------
      99 |1 |2010-11-03 10:05:29.39 |1234567890
      – the following update will cause the trigger to fire which should increment the counter column's value from 1 to 2 but it doesn't. The explanation is below
      update tab set timets = CURRENT_TIMESTAMP where ELEMENT_ID = 99;
      select * from tab;
      ELEMENT_ID |COUNT&|TIMETS |ALTERED_ID
      99 |1 |2010-11-03 10:05:38.343 |1234567890

      The update should have incremented the counter column to 2 but it remains at 1 because of invalid column positions in stored prepared statement created for trigger action.

      More background on general topic of trigger action plan can also be found at DERBY-4874

        Attachments

        1. repro.txt
          1 kB
          Mamta A. Satoor

          Issue Links

            Activity

              People

              • Assignee:
                mamtas Mamta A. Satoor
                Reporter:
                mamtas Mamta A. Satoor
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: