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] [Updated] (DERBY-6383) Update trigger defined on one column fires on update of other columns
Date Fri, 08 Nov 2013 18:18:17 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6383?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mamta A. Satoor updated DERBY-6383:
-----------------------------------

    Attachment: DERBY6383_patch3_diff.txt

DERBY6383_patch3_diff.txt which has number of junit tests, mainly to test out add/drop column's
impact on triggers. Want to point out that from drop column test case in testDerby6383StatementTriggerBugTst1,
in trunk, the drop column drops the statement trigger defined at  table level in trunk but
not in 10.6. This may be what DERBY-3940(Dropping a column does not drop triggers which mention
that column) is. DERBY-3940 has been verified to be fixed in trunk and 10.10 but we do not
for sure for earlier codelines. I will work on trying out the test case attached to DERBY-3940
on 10.6 and other higher codelines. But if anyone knows about it, may be they can explain
the difference in behavior of drop column on trigger table in trunk vs 10.6 Thanks
+
+        //Drop a column from the table. Following will drop trigger t3
+        // because it depends on column being dropped. But trigger t2
+        // will remain intact since it does not have dependency on
+        // column being dropped. So only trigger left at this point
+        // will be t2 after the following column drop
+        s.executeUpdate("ALTER TABLE DERBY_6368_TAB1 DROP COLUMN Y");


> 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, DERBY6383_patch3_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, 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