Re: Firing Orders - Mailing list pgsql-general

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