Re: ON DELETE trigger blocks delete from my table

On 10/25/2004 3:33 PM, Franco Bruno Borghesi wrote:

> I've made a test case, and setting the trigger BEFORE DELETE doesn't
> delete the rows from the table (but it does execute the trigger, and it
> does insert the rows in the audit table), I dont' know why :(.

Because the internal variable for NEW is initialize to NULL and
returning NULL from a BEFORE trigger silently suppresses the operation
on the original row that it was fired for.

Jan

>
> Anyway, setting the trigger AFTER DELETE works ok.
>
> On Mon, 2004-10-25 at 15:56, Naeem Bari wrote:
>
>> Hi,
>>
>>
>>
>> I am using postgres 7.4.5 on Redhat Enterprise Linux 3.
>>
>>
>>
>> My background is really on Oracle, and I am porting a largish database
>> over to postgres.
>>
>>
>>
>> Here is my problem:
>>
>>
>>
>> On oracle, I had a table with an “on update or delete” trigger that
>> copied the current row out to an audit table. Works like a champ. On
>> postgres, when I try to delete a row, all it gives back to me is
>> “DELETE 0” and does nothing.
>>
>>
>>
>> Here is the text of the trigger:
>>
>>
>>
>> ~~~~~~~~~~
>>
>> 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;
>>
>> ~~~~~~~~~~
>>
>>
>>
>> Any help would be appreciated!
>>
>>
>>
>> Thanks,
>>
>> naeem
>>
>>
>

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