[
https://issues.apache.org/jira/browse/DERBY-6383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13810528#comment-13810528
]
Mamta A. Satoor commented on DERBY-6383:
----------------------------------------
Knut, you are right about upgrade test demonstrating what does not work in
releases with defect DERBY-6383 and newly created triggers on soft-upgraded or
hard-upgraded triggers working correctly. I will go ahead and remove the
upgrade test so we don't need to find a workaround to DERBY-4577. Also, I will
go ahead and create a new jira to see if we can add another column to
SYSTRIGGERS table to save the CREATE TRIGGER sql for cases like this jira. This
saved sql can be used in tiring to fix the bugs by recreating the SYSTRIGGERS
row information if needed during soft-upgrade or hard-upgrade. In this jira,
the only way to fix the problem is to manually drop and recreate the STATEMENT
triggers after sof/hard upgrade since we do not have information on what
columns the CREATE TRIGGER was created on.
> 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
> Assignee: Mamta A. Satoor
> Attachments: DERBY6383_patch1_diff.txt, DERBY6383_patch2_diff.txt,
> d6383.sql, derby_for_Embedded_40Changes10_7testTriggers.log,
> derby_for_Embedded_40Changes10_9testDisposableStatisticsExplicit.log,
> error-stacktrace_for_Embedded_40Changes10_7testTriggers.out,
> error-stacktrace_for_Embedded_40Changes10_9testDisposableStatisticsExplicit.out,
> runall.out
>
>
> 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)
