Re: ON DELETE trigger blocks delete from my table

Ok, a really newbie question - I think I will switch to using "after"
rather than "before" - but can I modify the trigger statement without
dropping the trigger function? The reason I ask is that I actually wrote
a program that takes oracle's DDL and generates all the tables, audit
tables, triggers and sequences that I need. So I really have like 50
tables that are affected by this issue - would much rather modify my
program than hand fix 50 problems :)

BTW, I did not find much that did what my program does. Or maybe I did
not look hard enough? Basically my program eats "meta ddl" (pseudo ddl
that I cam up with that specifies the table name, the columns, the
column that should be a sequence number, whether a table should be
audited or not, plus table and column comments) and spits out DDL for
both oracle and postgres. Keeps my DDL all nice and neat and consistent,
and I have to write only a small amount of DDL to generate a lot of it
:)

Thanks again for the help guys,
Naeem

-----Original Message-----
From: Jan Wieck [mailto:JanWieck(at)Yahoo(dot)com]
Sent: Monday, October 25, 2004 2:52 PM
To: Franco Bruno Borghesi
Cc: Naeem Bari; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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 #