db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4984) ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.
Date Wed, 16 Feb 2011 01:07:57 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Dag H. Wanvik updated DERBY-4984:
---------------------------------

    Urgency: Normal
     Labels: derby_triage10_8  (was: )

> ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column
getting dropped.
> ---------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4984
>                 URL: https://issues.apache.org/jira/browse/DERBY-4984
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0,
10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1
>            Reporter: Mamta A. Satoor
>              Labels: derby_triage10_8
>
> While doing testing for DERBY-4887, I found a case where ALTER TABLE DROP COLUMN will
leave triggers in invalid state even if those triggers are not using the column getting dropped.
eg
> CREATE TABLE tab ( 
>        element_id INTEGER NOT NULL, 
>        altered_id VARCHAR(30) NOT NULL
> ); 
> insert into tab values(1,'aa');
> -- Create a trigger against the table 
> CREATE TRIGGER mytrig 
>  AFTER UPDATE OF altered_id ON tab 
>  REFERENCING NEW AS newt OLD AS oldt 
>  FOR EACH ROW MODE DB2SQL 
>   SELECT newt.altered_id from tab;
> --Drop the first column in the table. This will cause the column positions to be recalculated
within the table
> alter table tab drop column element_id; 
> --mytrig is still looking for column altered_id at position 2 but drop column has changed
it's position within the table to 1
> update tab set altered_id='bb';
> As shown in the example above, table "TAB" only has 2 columns. The trigger "MYTRIG" uses
the 2nd column in it's trigger action through the REFERENCING clause. During trigger action
sql parsing, every column referenced through REFERENCING clause gets transformed into a reference
to the column through it's column position in the trigger table(this change to look for columns
based on their column positions rather than the name went in as revision 397959 with following
commit comments DERBY-1258 Change the generated code for a new/old column in a row trigger
to access columns by position and not name to avoid the case-insensitive name lookup specified
by JDBC.) When in the script above, we drop the column in position 1, the trigger "MYTRIG"
ends up becoming invalid because column being used in the trigger action is no more in column
position 2.
> One possible solution is to regenerate the SPSDescriptor associated with the trigger
action for all the triggers defined on the table whose column is getting dropped. We could
be little smarter and only regenerate the SPSDescriptor for the triggers who use the REFERENCING
clause. But we need to do more testing to make sure that triggers without REFERENCING clause
do not get impacted by a drop of column which is not the last column of the table. This optimization
of recognizing the right triggers may not be worth it since performance may not be that big
a criteria for an ALTER TABLE DROP COLUMN which should be a rare operation in a production
system.
> An interim solution to this problem is obviously to drop and recreate the triggers

-- 
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message