Re: ON DELETE trigger blocks delete from my table

From: "Naeem Bari" <naeem(dot)bari(at)agilissystems(dot)com>
To: <pgsql-general(at)postgresql(dot)org>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: ON DELETE trigger blocks delete from my table
Date: 2004-10-25 19:53:25
Message-ID: 53F35087CC531844AD19CCAE6FA66929368A@util.agilissystems.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, October 25, 2004 2:48 PM
To: Naeem Bari
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] ON DELETE trigger blocks delete from my table

"Naeem Bari" <naeem(dot)bari(at)agilissystems(dot)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Naeem Bari 2004-10-25 20:09:30 Re: ON DELETE trigger blocks delete from my table
Previous Message Jan Wieck 2004-10-25 19:52:22 Re: ON DELETE trigger blocks delete from my table