[DERBY-6387] Incorrect ordering of triggers in dblook

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.10.1.1
• Fix Version/s: 10.10.2.0, 10.11.1.1
• Component/s: Tools
• Issue & fix info: Repro attached
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Created: 22/Oct/13 12:48
• Updated: 21/Jan/15 00:23
• Resolved: 06/Mar/14 17:26

Description

The order in which triggers fire, is partly determined by the order in which they are created.

See here:

http://db.apache.org/derby/docs/10.10/ref/rrefsqlj43125.html

When multiple triggers are defined for the same database event for

the same table for the same trigger time (before or after), triggers

are fired in the order in which they were created.

And here:

SQL:2003, part 2, section 4.38.1 General description of triggers:

The order of execution of a set of triggers is ascending by value of

their timestamp of creation in their descriptors, such that the

oldest trigger executes first.

Since the order of creation matters, dblook should generate CREATE TRIGGER statements in an order that preserves the original order of execution.

dblook currently generates CREATE TRIGGER statements in the order returned by a SELECT query against SYS.SYSTRIGGERS. The SELECT query does not have an ORDER BY clause, so it is not guaranteed to return the rows in a specific order.

Issue Links

• is related to: DERBY-6450 dblook_test_territory nightly test failure
• is related to: DERBY-6481 Derby 10.10 backport issue (winter/spring 2014)