Skip site navigation (1) Skip section navigation (2)

Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order

From: David Pufer <david(dot)pufer(at)7data(dot)eu>
To: pgsql-docs(at)postgresql(dot)org
Subject: Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
Date: 2007-04-21 16:48:34
Message-ID: 462A4062.3070704@7data.eu (view raw or flat)
Thread:
Lists: pgsql-docs
Tom Lane wrote:
> Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
>   
>> On Apr 21, 2007, at 4:39 , David Pufer wrote:
>>     
>>> In my opinion it would be nice to mention in SQL reference commands that
>>> constraint triggers created by CREATE CONSTRAINT TRIGGER are fired in
>>> creation order unlike regular triggers created by command CREATE
>>> TRIGGER. As I know, the firing order of the CONSTRAINT TRIGGER is
>>> determined by the XXXXX num at "RI_ConstraintTrigger_XXXXX" column in
>>> pg_trigger table.
>>>       
>
>   
>> I believe this just falls out of the normal order of trigger firing  
>> as explained in the CREATE TRIGGER documentation (http:// 
>> www.postgresql.org/docs/8.2/interactive/sql-createtrigger.html)
>>     
>
> The proposed change is wrong anyway: AFAICS from looking at the code,
> an RI_ConstraintTrigger_XXXXX name is only generated for triggers that
> are created in support of FOREIGN KEY constraint syntax.  If you use
> CREATE CONSTRAINT TRIGGER then the trigger name is whatever you say it
> is.  I don't recall if it was always like that, but that's definitely
> the behavior in CVS HEAD.
>
> It would be a mistake to assume that FK constraint triggers are fired in
> creation order anyway, since the lexical sorting of strings isn't the
> same as numeric order (not to mention OID wraparound issues).
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>   
I tested in in 8.2.3 stable.
e.g. command

CREATE CONSTRAINT TRIGGER my_constraint_trigger_name
AFTER INSERT OR UPDATE OR DELETE
ON my_test_table
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE my_test_procedure()

created a row with tgname "RI_ConstraintTrigger_28329" and tgconstrname
"my_constraint_trigger_name" in system catalog in table pg_trigger,
while creation of normal trigger (not constraint trigger) puts its name
into tgname column. As Tom notedm a while ago it might be different in
CVS HEAD and consequently in 8.3.

Thanks for helpful info
David Pufer





In response to

Responses

pgsql-docs by date

Next:From: Michael GlaesemannDate: 2007-04-21 17:37:07
Subject: Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
Previous:From: Tom LaneDate: 2007-04-21 16:20:40
Subject: Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group