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, 07 Nov 2013 19:11:18 GMT

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

Mime
View raw message