Complex trigger firing order - Mailing list pgsql-docs

I hope the firing order of triggers can be introduced by the
document in more detail.

I am raising this requests because I personally feel it is
very important to understand the exact firing order of
various triggers that come with mixed types and names.

The following snippet are the Q&A posted in November 2002.
It's so precious to myself and I dare not erase them from my
archive so far mainly because I don't find any better
descriptions for the aformentioned technique in existing
document than these Q&A.

Best Regards,

CN

=====My questions follows===============
The document reads:

"If more than one trigger is defined for the same event on
the same
relation, the triggers will be fired in alphabetical order
by name."

Q1. Are BEFORE UPDATE and BEFORE DELETE (let's call them as
"customed")
triggers always fired before RI_ConstraintTriggers on the
same relation
regardless of the alphabetical name of these customed
triggers?

Q2. Are BEFORE UPDATE and BEFORE DELETE (customed) triggers
on the
referenced relation fired before the RI_ConstraintTriggers
on the
referencing relation regardless of the alphabetical name of
these
customed triggers?

Q3. Are AFTER UPDATE and AFTER DELETE (customed) triggers
fired after
RI_ConstraintTriggers on the same relation regardless of the
alphabetical
name of these customed triggers?

Q4. Are AFTER UPDATE and AFTER DELETE (customed) triggers on
the
referenced relation fired after the RI_constraintTriggers on
the
referencing relation regardless of the alphabetical name of
these
customed triggers?

It might be a good idea to add matrix tables with explaining

(1) triggers' firing orders when BEFORE and AFTER are
involved like the
aformentioned questions
(2) UPDATE and DELETE order between the referenced (master)
and the
referencing (detail) tables when RI constraints are enforced
between the
two

to programmer's manual as I feel programmers may fail to
maintain their
data in integrity if they lose the control over these
"orders".

=======Answers from Tom follows=============
> Q1. Are BEFORE UPDATE and BEFORE DELETE (let's call them
as "customed")
> triggers always fired before RI_ConstraintTriggers on the
same relation
> regardless of the alphabetical name of these customed
triggers?

The RI_ triggers are AFTER triggers, so yes.

> Q2. Are BEFORE UPDATE and BEFORE DELETE (customed)
triggers on the
> referenced relation fired before the RI_ConstraintTriggers
on the
> referencing relation regardless of the alphabetical name
of these
> customed triggers?

Huh?  They would not be fired at all, at least not as part
of the same
trigger event.

> Q3. Are AFTER UPDATE and AFTER DELETE (customed) triggers
fired after
> RI_ConstraintTriggers on the same relation regardless of
the alphabetical
> name of these customed triggers?

When it says "alphabetical order", it means exactly that.
RI triggers
aren't special.

======Answers from Stephan Szabo follows==========
> "If more than one trigger is defined for the same event on
the same
> relation, the triggers will be fired in alphabetical order
by name."

I think event in this case probably is meant to include the
BEFORE/AFTER
state.

> Q1. Are BEFORE UPDATE and BEFORE DELETE (let's call them
as "customed")
> triggers always fired before RI_ConstraintTriggers on the
same relation
> regardless of the alphabetical name of these customed
triggers?

Yes, because before triggers happen at a different stage of
the
processing.

>
> Q3. Are AFTER UPDATE and AFTER DELETE (customed) triggers
fired after
> RI_ConstraintTriggers on the same relation regardless of
the alphabetical
> name of these customed triggers?

No it's in alpha order.  I believe technically this isn't
compliant by
SQL99 14.14/14.20, but it may be more useful. ;)

> Q2. Are BEFORE UPDATE and BEFORE DELETE (customed)
triggers on the
> referenced relation fired before the RI_ConstraintTriggers
on the
> referencing relation regardless of the alphabetical name
of these
> customed triggers?
> Q4. Are AFTER UPDATE and AFTER DELETE (customed) triggers
on the
> referenced relation fired after the RI_constraintTriggers
on the
> referencing relation regardless of the alphabetical name
of these
> customed triggers?

I don't get it.  Since the referencing constraint triggers
do only
selects, the referenced relation shouldn't be firing
triggers.  If you
meant the other directly.

If you meant the other direction when there's an action
being done, I
think it basically goes:
Before triggers on referenced
Action on referenced (update/delete)
After triggers on referenced before RI trigger
- Before triggers on referencing
- Action on referencing
After triggers on referenced after RI
After triggers on referencing (which may also have RI)

========My questions follows===========
> > "If more than one trigger is defined for the same event
on the same
> > relation, the triggers will be fired in alphabetical
order by name."
>
> I think event in this case probably is meant to include
the BEFORE/AFTER
> state.
>
> > Q1. Are BEFORE UPDATE and BEFORE DELETE (let's call them
as "customed")
> > triggers always fired before RI_ConstraintTriggers on
the same relation
> > regardless of the alphabetical name of these customed
triggers?
>
> Yes, because before triggers happen at a different stage
of the
> processing.
>
> >
> > Q3. Are AFTER UPDATE and AFTER DELETE (customed)
triggers fired after
> > RI_ConstraintTriggers on the same relation regardless of
the alphabetical
> > name of these customed triggers?
>
> No it's in alpha order.  I believe technically this isn't
compliant by
> SQL99 14.14/14.20, but it may be more useful. ;)
>

CREATE TABLE master (mc1 TEXT PRIMARY KEY,mc2 TEXT);

CREATE TABLE detail (dc1 TEXT
,dc2 TEXT
,PRIMARY KEY (dc1,dc2)
,CONSTRAINT detail_ri FOREIGN KEY (dc1) REFERENCES master
(mc1) ON UPDATE
CASCADE ON DELETE CASCADE);

> > Q2. Are BEFORE UPDATE and BEFORE DELETE (customed)
triggers on the
> > referenced relation fired before the
RI_ConstraintTriggers on the
> > referencing relation regardless of the alphabetical name
of these
> > customed triggers?

CREATE TRIGGER master_before_tg BEFORE UPDATE ON master FOR
EACH ROW
EXECUTE PROCEDURE master_before_func();

> > Q4. Are AFTER UPDATE and AFTER DELETE (customed)
triggers on the
> > referenced relation fired after the
RI_constraintTriggers on the
> > referencing relation regardless of the alphabetical name
of these
> > customed triggers?

CREATE TRIGGER master_after_tg AFTER UPDATE ON master FOR
EACH ROW
EXECUTE PROCEDURE master_after_func();

>
> I don't get it.  Since the referencing constraint triggers
do only
> selects, the referenced relation shouldn't be firing
triggers.  If you
> meant the other directly.

INSERT INTO master VALUES ('x','y');
INSERT INTO master VALUES ('x','aaa');

My question is when

UPDATE master SET mc1='xx',mc2='yy';

is executed,
(q11) Is master_before_func() executed before dc1's value
gets changed to
"xx"?
(q12) Does master_before_func() sees "x" or "xx" in dc1?
(q21) Is master_after_func() executed after dc1's value gets
changed to
"xx"?
(q22) Does master_after_func() sees "x" or "xx" in dc1?

I also use many triggers on DELETE event like this example
whose
behaviors, and the data they produce, are still unknown to
me. I also
have the following triggers without knowing what values in
mc2 will be
seen by detail_before_func() and detail_after_func():

CREATE TRIGGER detail_before_tg BEFORE UPDATE ON detail FOR
EACH ROW
EXECUTE PROCEDURE detail_before_func();
CREATE TRIGGER detail_after_tg AFTER UPDATE ON detail FOR
EACH ROW
EXECUTE PROCEDURE detail_after_func();

>
> If you meant the other direction when there's an action
being done, I
> think it basically goes:
> Before triggers on referenced
> Action on referenced (update/delete)
> After triggers on referenced before RI trigger
>  - Before triggers on referencing
>  - Action on referencing
> After triggers on referenced after RI
> After triggers on referencing (which may
>  also have RI)

========Answers from Stephan follows==========
My main email address is currently dead (hosting machine has
died), so
I'm sending from another address.

>INSERT INTO master VALUES ('x','y');
>INSERT INTO master VALUES ('x','aaa');

>My question is when

>UPDATE master SET mc1='xx',mc2='yy';

>is executed,
>(q11) Is master_before_func() executed before dc1's value
gets changed to
>"xx"?
>(q12) Does master_before_func() sees "x" or "xx" in dc1?
>(q21) Is master_after_func() executed after dc1's value
gets changed to
>"xx"?
>(q22) Does master_after_func() sees "x" or "xx" in dc1?

The order in this case should be (IIRC - I think it just
uses strcmp for
the comparison so 'R' is before 'm')

master_before_func() -- this is a before trigger
master_before_func() -- there are two rows, so it gets run
twice
Rows in master are set as xx
RI_Constraint_Trigger_<blah>
  detail_before_tg
  Update is run for the first row dc1='xx'
RI_Constraint_Trigger_<blah>
  detail_before_tg
  Update is run for second row dc1='xx'
master_after_func()
master_after_func()
RI_Constraint_Trigger_<blah2> - check the update
RI_Constraint_Trigger_<blah2> - check the update
detail_after_func()
detail_after_func()

So, I believe

q11 yes, and in fact it could change what the update was to.
q12 'x'
q21 In this case yes.
q22 'xx'

Note that if the trigger name was "Master_after_tg" (with
the quotes),
then the order of the after trigger and the constraint
trigger would be
reversed and it would see 'x' in dc1.

All of the detail_*_func should see mc1='xx' since even the
before
triggers on that occur after the update happens.  In the
pure SQL model,
if I read it right, the first before trigger should see one
row of 'xx'
(the one that this row is dependant on) and the other as
'x', but we don't
comply with that.