From: | "CN" <cnliou9(at)fastmail(dot)fm> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Firing Orders |
Date: | 2002-11-29 05:09:47 |
Message-ID: | 20021129050947.A85773E604@server2.fastmail.fm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | suresh s | 2002-11-29 05:52:50 | Re: Createlang Pltclu failed |
Previous Message | CN | 2002-11-29 03:56:41 | Re: Server v7.3RC2 Dies |