Re: triggers

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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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
>