Subject: Complex trigger firing order

From: "cnliou" <[email-masked]>
Date: Wed, 01 Sep 2004 21:21:01 +0800 (CST)

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.

---

From: Josh Berkus <[email-masked]>
Date: Thu, 2 Sep 2004 10:26:44 -0700

CN,

> I hope the firing order of triggers can be introduced by the

This is good material.  Can you re-write it as  documentation?   I can see 
putting it either in the main docs or the FAQ.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco