db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-4887) ALTER TABLE DROP COLUMN leaves the dependent trigger invalid rather than drop it
Date Fri, 13 Sep 2013 09:35:53 GMT

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

Knut Anders Hatlen commented on DERBY-4887:
-------------------------------------------

When I run repro.txt against 10.10, I now see what I believe is the expected behaviour: ALTER
TABLE ... DROP COLUMN with the default CASCADE semantics causes the trigger to be dropped
too, and a warning is generated. If the ALTER TABLE ... DROP COLUMN statement is changed to
use RESTRICT semantics, it refuses to drop the column because the trigger depends on it.

I think this means the work on this issue is done. If there's more work to be done, please
feel free to reopen this issue and provide information about what is missing, add a comment
to one of the other issues that track similar problems (like DERBY-2041 or DERBY-3940), or
file a separate JIRA issue for the remaining work.
                
> 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
>    Affects Versions: 10.6.1.0
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>              Labels: derby_triage10_8
>         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.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message