[jira] Issue Comment Edited: (DERBY-4887) ALTER TABLE DROP COLUMN leaves the depedent trigger invalid rather than drop it

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-4887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12982801#action_12982801 ] 

Mamta A. Satoor edited comment on DERBY-4887 at 1/17/11 2:07 PM:
-----------------------------------------------------------------

I looked at the Reference manual for 10.3 which is where I think ALTER TABLE DROP COLUMN was introduced and it says that "The schema objects which can cause a DROP COLUMN RESTRICT to be rejected include: views, triggers, primary key constraints, foreign key constraints, unique key constraints, check constraints, and column privileges. If one of these types of objects depends on the column being dropped, DROP COLUMN RESTRICT will reject the statement." Based on this, for this jira, for RESTRICT, we should not allow DROP COLUMN if a trigger is using the column being dropped. 

For CASCADE, the reference manuak says "If you specify CASCADE, then the column drop should additionally drop other schema objects which have become invalid." Based on this, we should go ahead and drop the trigger if the trigger is using the column being dropped.


      was (Author: mamtas):
    I looked at the Reference manual for 10.3 which is where I think ALTER TABLE DROP COLUMN was introduced and it says that "The schema objects which can cause a DROP COLUMN RESTRICT to be rejected include: views, triggers, primary key 
constraints, foreign key constraints, unique key constraints, check constraints, and column privileges. If one of these types of objects depends on the column being dropped, DROP COLUMN RESTRICT will reject the statement." Based on this, 
for this jira, for RESTRICT, we should not allow DROP COLUMN if a trigger is using the column being dropped. 

For CASCADE, the reference manuak says "If you specify CASCADE, then the column drop should additionally drop other schema objects which have become invalid." Based on this, we should go ahead and drop the trigger if the trigger is using the column being dropped.

  
> 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
>         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.