[
https://issues.apache.org/jira/browse/DERBY-4887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dag H. Wanvik updated DERBY-4887:
---------------------------------
Summary: ALTER TABLE DROP COLUMN leaves the dependent trigger invalid
rather than drop it (was: ALTER TABLE DROP COLUMN leaves the depedent trigger
invalid rather than drop it)
> ALTER TABLE DROP COLUMN leaves the dependent trigger invalid rather than drop
> it
> --------------------------------------------------------------------------------
>
> Key: DERBY-4887
> URL: https://issues.apache.org/jira/browse/DERBY-4887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
> Attachments: repro.txt
>
>
> 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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
