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