Re: triggers - Mailing list pgsql-general

I've got my 'cross/mutual foreign keys'working fine.

rough syntax
---------------
create table email (
   email_id serial primary
   emails
)

create table usr(
   usr_id serial primary
   name
   usr_email_id_pri
)

create table usr_emails(
   usr_email_id serial primary
   usr_id
   email_id
)
alter table usr foreign key(usr_email_id_pri)
   references (usr_emails.usr_email_id) deferrable initially deferred

alter table usr_emails foreign key(usr_id)
   references (usrs.usr_id) deferrable initially deferred

alter table usr_emails foreign key(email_id)
   references (emails.email_id) deferrable initially deferred
-------------------
end rough syntax

This works fine, I can insert the usr, the email if needed, and a new usr_email all in a
transaction and the foreign keys wait till the commit to do their check.

I would like a trigger or check constraint that checked that the fields:

    usrs.usr_email_id_pri
    usr_emails.usr_id

matched for any insert, update, or delete. It is possible to insert a usr with an existing
usr_email_id_pri field from usr_emails and also to insert a usr_email with an existing email_id
from some other usr.

I was looking for some way that a check or trigger would defer until the end of the transaction.
Even inside of a transaction, an AFTER INSERT trigger fires immediately after the insert, so it
didn't work.


3/6/2003 12:07:49 PM, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

>Dennis Gearon wrote:
>> So,
>>     I can make all the changes I want within a transaction, and no related triggers fire before
>> the changes are are committed, right?
>>     The triggers work on the image of the data before the transaction is BEGINs,
>> PLUS what it would look like with ALL UPDATES/DELETES in the tranaction being successfull?
>>     And there's now way to tell which triggers on what tables affected in the transaction will
>> fire first, right?
>
>In 7.3, triggers fire in alphabetical order, as do rules.
>
>AFTER triggers are most needed when you need to see a value assigned by
>the primary command, like the sequence number assigned to a column.
>
>--
>  Bruce Momjian                        |  http://candle.pha.pa.us
>  pgman@candle.pha.pa.us               |  (610) 359-1001
>  +  If your life is a hard drive,     |  13 Roberts Road
>  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>