ALTER TABLE DROP COLUMN leaves the depedent 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
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.
