[
https://issues.apache.org/jira/browse/DERBY-4984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4984:
-----------------------------------
Labels: derby_backport_reject_10_5 derby_backport_reject_10_6
derby_backport_reject_10_7 derby_triage10_8 (was: derby_triage10_8)
> 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
> Assignee: Mamta A. Satoor
> Labels: derby_backport_reject_10_5, derby_backport_reject_10_6,
> derby_backport_reject_10_7, derby_triage10_8
> Fix For: 10.7.1.4, 10.8.2.2
>
>
> 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.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
