Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 7 Nov 2013 19:11:18 +0000 (UTC)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12674782.1382358808226.36564.1383851478339@arcas>
In-Reply-To: <JIRA.12674782.1382358808226@arcas>
References: <JIRA.12674782.1382358808226@arcas>
Subject: [jira] [Commented] (DERBY-6383) Update trigger defined on one
 column fires on update of other columns
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Mamta A. Satoor commented on DERBY-6383:
----------------------------------------

I spent some time analyzing the triggers and systriggers system table to see if we can help identify precisely which triggers need to be dropped and recreated. 

systriggers table has a column called REFERENCEDCOLUMNS which keeps track of the columns on which triggers should get fired.

For update triggers defined at statement level on specific columns, this column will keep position of those columns in the REFERENCEDCOLUMNS column. eg
create table t1(x int, y int);
create table t2(x int, y int);
create trigger tr1 after update of x on t1 referencing old table as old insert into t2 select * from old;
REFERENCEDCOLUMNS for the above trigger should be (1) but because of DERBY-6383, it is (1,2) and thus causing the trigger to fire no matter which column gets updated rather than just for column x. I have added a junit test case for this scenario showing that after the fix for DERBY-6383 is checked in, we do not fire the trigger above for update of column y. Although, if a new column is added to the column after the trigger above is created, an update of the newly added column will not fire the trigger defined above because 
REFERENCEDCOLUMNS does not have the position of the newly added column listed in it. I will add a junit test for newly added column for update triggers defined at statement level on specific columns.

For update triggers defined at statement level on table level rather than specific columns, this column should be null eg
create trigger tr2 after update on t1 referencing old table as old insert into t2 select * from old;
REFERENCEDCOLUMNS for the above trigger should be null since it should fire for any column update but because of DERBY-6383, it is (1,2) which does not cause any problem for the given eg because we want this trigger to fire whether column x or y got updated. But if we add another column to table t1, an update of that column will not fire the trigger above because REFERENCEDCOLUMNS is not null. I will add a junit test case for this scenario to show that after the fix for DERBY-6383 is checked in, the trigger tr2 will get fired for an update of new column added after the trigger creation.

I will also add junit test cases for drop column scenario to confirm update triggers work fine.

Based on this information, all statement level update triggers will need to be dropped and recreated in order to fix the buggy triggers.

> 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
>             Fix For: 10.9.2.2, 10.10.1.3, 10.11.0.0
>
>         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, releaseNote.html, 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)