[
https://issues.apache.org/jira/browse/DERBY-6383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13802860#comment-13802860
]
Knut Anders Hatlen commented on DERBY-6383:
-------------------------------------------
It looks like the behaviour was changed in this commit:
{quote}
r1044096 | mamta | 2010-12-09 20:19:42 +0100 (Thu, 09 Dec 2010) | 14 lines
DERBY-4874 Trigger does not recognize new size of VARCHAR column expanded with
ALTER TABLE. It fails with ERROR 22001: A truncation error was encountered
trying to shrink VARCHAR
The trigger action associated with a trigger gets converted as shown in the
example below. This transformation happens if the trigger action has REFERENCEs
clause.
update xr.repositoryobjectversion set uname = upper( n.name )
where name = n.name and uname <> upper( n.name );
turns into
update xr.repositoryobjectversion set uname =
upper( CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(3)
AS VARCHAR(128)) )
where name = CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(3)
AS VARCHAR(128))
As can be seen above, there is a CASTing involved which uses the length of the
column in trigger table.
If say that length is changed by ALTER TABLE after the trigger has been
created, that change in the length does not get reflected in the sql associated
with the trigger action in the form on SPSDescriptor. In order to fix this, I
have made changes which willcause us to regenerate the sql from the trigger
action for the SPSDescriptor if we are working with an invalidated row level
trigger which uses the REFERENCEs clause.
{quote}
> Update trigger defined on one column fires on update of other columns
> ---------------------------------------------------------------------
>
> Key: DERBY-6383
> URL: https://issues.apache.org/jira/browse/DERBY-6383
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.7.1.4, 10.8.1.2, 10.9.1.0, 10.10.1.1
> Reporter: Knut Anders Hatlen
> Attachments: d6383.sql
>
>
> I see this problem on 10.8 and higher. To reproduce, create a database with a
> trigger like this:
> connect 'jdbc:derby:trigdb;create=true';
> create table t1(x int, y int);
> create table t2(x int, y int);
> create trigger tr after update of x on t1 referencing old table as old insert
> into t2 select * from old;
> Then run dblook on the database, and you'll see the following output:
> -- ----------------------------------------------
> -- DDL Statements for triggers
> -- ----------------------------------------------
> CREATE TRIGGER "APP"."TR" AFTER UPDATE OF "X", "Y" ON "APP"."T1" REFERENCING
> OLD_TABLE AS OLD FOR EACH STATEMENT insert into t2 select * from old;
> Notice that the DDL creates an update trigger for columns X and Y, whereas
> the original trigger was defined on column X only.
> I see the expected DDL on 10.7.1.1.
--
This message was sent by Atlassian JIRA
(v6.1#6144)
