db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6383) Update trigger defined on one column fires on update of other columns
Date Thu, 31 Oct 2013 18:33:17 GMT

    [ 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)

Mime
View raw message