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

Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: David Pufer <david(dot)pufer(at)7data(dot)eu>
Cc: pgsql-docs(at)postgresql(dot)org
Subject: Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
Date: 2007-04-21 17:37:07
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-docs
On Apr 21, 2007, at 11:48 , David Pufer wrote:

> 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://  
>> 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.

<snip />

> As Tom notedm a while ago it might be different in CVS HEAD and  
> consequently in 8.3.

Just for my own confirmation, the behavior is different in HEAD.

postgres=# create table a_table (a_id integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"a_table_pkey" for table "a_table"
postgres=# create function a_trigger_function () returns trigger  
language plpgsql as $$ begin return null; end;$$;
postgres=# create constraint trigger a_constraint_trigger after  
insert or update or delete on a_table deferrable initially deferred  
for each row execute procedure a_trigger_function();
postgres=# select tgname, tgconstrname from pg_trigger;
          tgname          |     tgconstrname
pg_sync_pg_database     |
pg_sync_pg_authid       |
pg_sync_pg_auth_members |
a_constraint_trigger    | a_constraint_trigger
(4 rows)

postgres=# select version();
PostgreSQL 8.3devel on powerpc-apple-darwin8.9.0, compiled by GCC  
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.  
build 5367)
(1 row)

A very nice improvement!

Michael Glaesemann
grzm seespotcode net

In response to

pgsql-docs by date

Next:From: Guillaume LelargeDate: 2007-04-22 14:10:51
Subject: Patch for monitoring.sgml
Previous:From: David PuferDate: 2007-04-21 16:48:34
Subject: Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order

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