Thread: Firing Orders
Hi! 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". Regards, CN -- http://fastmail.fm - The way an email service should be
"CN" <cnliou9@fastmail.fm> writes:
> 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.
regards, tom lane
On Thu, 28 Nov 2002, CN wrote: > "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)
Thank you very much for the patience! Stephan,
>
> On Thu, 28 Nov 2002, CN wrote:
>
> > "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)
>
I will read this more carefully after my head cools down a little bit.
Best regards,
CN
--
http://fastmail.fm - Or how I learned to stop worrying and
love email again