Thread: triggers
are 'before' triggers or 'after' triggers more common? Why? What's the benefits, problems with each? Can a function in a trigger lock a table? Or is it just sessions that can lock a table?
On Tuesday, February 25, 2003, at 03:17 PM, Dennis Gearon wrote: > are 'before' triggers or 'after' triggers more common? it depends on what you want to do. In many situations you'll need both. > > Why? Before triggers are most useful when you need to change the value of a column *before* it is updated/inserted (using the NEW table). Or if you want to completely deny the operation (by RETURNing NULL from the trigger function). After triggers are useful when you need to modify table B based on the fact that something successfully happened in table A. You can guarantee that the after triggers only happen if the before trigger and the actual action successfully completed. > > What's the benefits, problems with each? > > Can a function in a trigger lock a table? Or is it just sessions that > can lock a table? Yes, they can lock tables. eric
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?
2/25/2003 1:15:09 PM, Eric B.Ridge <ebr@tcdi.com> wrote:
>On Tuesday, February 25, 2003, at 03:17 PM, Dennis Gearon wrote:
>
>> are 'before' triggers or 'after' triggers more common?
>
>it depends on what you want to do. In many situations you'll need both.
>
>>
>> Why?
>
>Before triggers are most useful when you need to change the value of a
>column *before* it is updated/inserted (using the NEW table). Or if
>you want to completely deny the operation (by RETURNing NULL from the
>trigger function).
>
>After triggers are useful when you need to modify table B based on the
>fact that something successfully happened in table A. You can
>guarantee that the after triggers only happen if the before trigger and
>the actual action successfully completed.
>
>>
>> What's the benefits, problems with each?
>>
>> Can a function in a trigger lock a table? Or is it just sessions that
>> can lock a table?
>
>Yes, they can lock tables.
>
>eric
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
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
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
>