db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-4887) ALTER TABLE DROP COLUMN leaves the depedent trigger invalid rather than drop it
Date Fri, 05 Nov 2010 16:44:42 GMT
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.


Mime
View raw message