db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6383) Update trigger defined on one column fires on update of other columns
Date Fri, 01 Nov 2013 15:25:24 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13811340#comment-13811340
] 

Knut Anders Hatlen commented on DERBY-6383:
-------------------------------------------

I think I'd prefer not to add an extra column to SYSTRIGGERS. If similar bugs turn up (fingers
crossed that they don't!), we can always tell users to drop and recreate the trigger after
upgrade, just like we'll tell them that they'll have to do that to work around this bug. I'm
worried that adding more redundancy in the system tables and more upgrade logic just in anticipation
of future bugs (bugs that we don't know what will look like) might cause more problems than
they will help if/when a situation where they are useful arises. In the case where a CREATE
operation is buggy, I'm fine with having to recreate the object to get it fixed. After all,
triggers are no special in this regard, so if we add such a column to SYSTRIGGERS, we should
probably add such columns to other system tables too in case CREATE TABLE, CREATE TYPE or
any other CREATE operation turns out to have a problem.

> 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