Re: ON DELETE trigger blocks delete from my table - Mailing list pgsql-general

On 10/25/2004 3:53 PM, Naeem Bari wrote:

> I understand. Makes sense. Is there anyway for my trigger function to
> "know" that it is being called on a delete or on an update? Because I do
> need to "return new" on update... and I really don't want to write 2
> different functions, one for update and one for delete...
>
> I would change the trigger to fire on "after" rather than before as Jan
> Weick suggests, but does that mean that if the trigger fails, the
> transaction would be committed anyways?

The variable TG_OP contains a string of 'INSERT', 'UPDATE' or 'DELETE'
as per the documentation:

     http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

Making it an AFTER trigger still ensures that the transaction is rolled
back if the trigger fails. What it also ensures is that no trigger fired
later can modify the NEW row after your auditing already took place. As
your trigger is, this doesn't matter to you. But as soon as you include
some new value in your auditing table you might be surprised not to find
that new value in the row.

In PostgreSQL a BEFORE trigger procedure on INSERT or UPDATE can modify
values in NEW because it is called BEFORE the new row is actually stored
in the table. This is usefull for enforcing timestamps, usernames,
derived values, you name it. AFTER triggers can't do that and are
guaranteed to see the values that really have been stored.


Jan


>
> Thanks for your help!
> naeem
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, October 25, 2004 2:48 PM
> To: Naeem Bari
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] ON DELETE trigger blocks delete from my table
>
> "Naeem Bari" <naeem.bari@agilissystems.com> writes:
>> CREATE OR REPLACE FUNCTION public.func_job_status_upd()
>>   RETURNS trigger AS
>> '
>> begin
>>   insert into x_job_status values ( OLD.job_id,
> OLD.job_status_type_id,
>> OLD.status_date, OLD.notes, OLD.edit_person_id, OLD.edit_date);
>>   return new;
>> end;
>> '
>>   LANGUAGE 'plpgsql' VOLATILE;
>
> If this is a BEFORE trigger, you probably need "RETURN OLD".  "NEW" will
> be NULL in a delete trigger, so you're returning NULL which cancels the
> operation.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #