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

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?

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