Trigger firing order problem on same position

[maxrode]

I came across a particular case of incorrect execution order on a table with the same positions Usually triggers with the same position from documentation are executed in alphabetical order

Link to documentation: https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref30/fblangref30-psql-triggers.html#fblangref30-psql-firingorder

With this example the trigger "ayyyo" is executed before the trigger "aaa$p" so it does not generate an error

But the correct order would be "aaa$p" and then "ayyyo"

Firebird Version : 3.0.10.33601

SQL Schema:

SET TERM ^;

CREATE SEQUENCE test_seq
^

CREATE EXCEPTION test_ex 'Error'
^

CREATE TABLE test_table (
  id bigint not null,
  description VARCHAR(100)
)
^

CREATE OR ALTER TRIGGER aaa$p FOR test_table
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.id IS NULL) THEN
    BEGIN
      EXCEPTION test_ex;
    END
END
^

CREATE OR ALTER TRIGGER ayyyo FOR test_table
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.id IS NULL) THEN
    BEGIN
      NEW.id = GEN_ID(test_seq, 1);
    END
END
^

Test (With no error):

INSERT INTO test_table(description) VALUES ('TEST')

What causes this behavior?

[aafemt]

The documentation is wrong. Firing order of triggers with the same position is unspecified.

[maxrode]

This seems like a big mistake given that it was serviced on 20 April 2024

[mrotteveel]

This seems like a big mistake given that it was serviced on 20 April 2024

Writing documentation is done by humans, and humans can make mistakes. I would consider this a minor mistake. Also, recency of a document in no way means that it is free of errors.

I guess this error can be traced back to the Firebird Book by Helen Borrie (e.g. second edition page 621), which for a long time was the only available reference. I guess that when the Russian version of the Firebird 2.5 Language Reference was written, that book was used as a reference, so it ended up there as well, and then in the translation to English, and from there to the 3.0, 4.0 and 5.0 version of the documentation.

I created https://github.com/FirebirdSQL/firebird-documentation/issues/213 to fix this in the documentation.

[mrotteveel]

While verifying this, I noticed that sometimes it does look like triggers are fired in alphabetical order, but then if you add more triggers, that seemingly alphabetical order breaks down.

[asfernandes]

Only before reloads the connection?

[mrotteveel]

No, the execution order is stable across reconnects, it is just that depending on the chosen names, and possibly on the order of creation, it might look alphabetical, but if you then add more triggers (either afterwards, or wholesale by recreating), you then notice that in fact it is not alphabetical.

[hvlad]

Triggers order is defined in RDB$RELATIONS.RDB$RUNTIME, and its put there as follows:

 *  Get the triggers in the right order, which appears
 *      to be system triggers first, then user triggers,
 *      then triggers that implement check constraints.

see setup_triggers() in dfw.epp.

Note, also: all queries there uses SORTED BY TRG.RDB$TRIGGER_SEQUENCE i.e. order does not includes RDB$TRIGGER_NAME