[DERBY-6383] Update trigger defined on one column fires on update of other columns

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.7.1.4, 10.8.1.2, 10.9.1.0, 10.10.1.1
• Fix Version/s: 10.7.1.4, 10.8.3.3, 10.9.2.2, 10.10.2.0, 10.11.1.1
• Component/s: SQL
• Labels: backport_reject_10_6
• Urgency: Normal
• Issue & fix info: Release Note Needed, Repro attached
• Bug behavior facts: Regression, Wrong query result
• Assignee: Mamta A. Satoor
• Reporter: Knut Anders Hatlen
• Created: 21/Oct/13 12:33
• Updated: 09/Sep/16 09:52
• Resolved: 14/Nov/13 07:28

Description

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.

Issue Links

• breaks: DERBY-6547 testDERBY5120NumRowsInSydependsForTrigger fails when the starting version of the upgrade trajectory is 10.10.2.0
• is broken by: DERBY-4874 Trigger does not recognize new size of VARCHAR column expanded with ALTER TABLE. It fails with ERROR 22001: A truncation error was encountered trying to shrink VARCHAR
• is related to: DERBY-6417 Dropping a column in 10.7 release when there is a trigger with REFERENCING OLD clause defined on the table gives error "ERROR 42X05: Table/View 'OLD' does not exist.".
• relates to: DERBY-6684 Failure in testDERBY5120NumRowsInSydependsForTrigger when upgrading from 10.11.1.0 to trunk