[jira] [Updated] (DERBY-6387) Incorrect ordering of triggers in dblook

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

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

Knut Anders Hatlen updated DERBY-6387:
--------------------------------------

    Attachment: create-triggers.sql

Here's how to reproduce (tested on 10.10.1.1 and trunk):

Create a database using the attached create-triggers.sql script. The script creates a number of triggers so that it fills up the first disk page in SYS.SYSTRIGGERS, then it drops most of the triggers so that the first page becomes empty. Finally, it creates more triggers so that the second page fills up, and it starts reusing space on the first page. Since the last triggers get inserted into the first page of SYS.SYSTRIGGERS, the unordered SELECT query will return the triggers out of creation order.

Running dblook on the created database will give this output:

{code:sql}
-- ----------------------------------------------
-- DDL Statements for triggers
-- ----------------------------------------------

CREATE TRIGGER "APP"."TR23" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR24" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR13" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR14" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR15" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR16" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR17" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR18" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR19" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR20" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR21" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);

CREATE TRIGGER "APP"."TR22" AFTER UPDATE OF "X" ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
{code}

Notice that the triggers TR23 and TR24 come first even though they were created last. If a new database is created using the dblook output, TR23 and TR24 will fire before the other triggers, but they should fire last.

> Incorrect ordering of triggers in dblook
> ----------------------------------------
>
>                 Key: DERBY-6387
>                 URL: https://issues.apache.org/jira/browse/DERBY-6387
>             Project: Derby
>          Issue Type: Bug
>          Components: Tools
>    Affects Versions: 10.10.1.1
>            Reporter: Knut Anders Hatlen
>         Attachments: create-triggers.sql
>
>
> 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.



--
This message was sent by Atlassian JIRA
(v6.1#6144)