db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6387) Incorrect ordering of triggers in dblook
Date Tue, 22 Oct 2013 12:58:41 GMT

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

Mime
View raw message