ON DELETE trigger blocks delete from my table

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

re: ON DELETE trigger blocks delete from my table

On 10/25/2004 2:56 PM, Naeem Bari wrote:
[color=blue]
> 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;[/color]

There is no NEW row on DELETE. You can either let the trigger fire
AFTER, causing its return value to be ignored, or define different
trigger procedures for UPDATE/DELETE, or you can check inside the
trigger for which event it was actually fired and return NEW/OLD
accordingly.


Jan
[color=blue]
> end;
> '
> LANGUAGE 'plpgsql' VOLATILE;
> ~~~~~~~~~~
>
> Any help would be appreciated!
>
> Thanks,
> naeem
>[/color]


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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

re: ON DELETE trigger blocks delete from my table

"Naeem Bari" <naeem.bari@agilissystems.com> writes:[color=blue]
> 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;[/color]

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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

re: ON DELETE trigger blocks delete from my table

On 10/25/2004 3:33 PM, Franco Bruno Borghesi wrote:
[color=blue]
> 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 :(.[/color]

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
[color=blue]
>
> Anyway, setting the trigger AFTER DELETE works ok.
>
> On Mon, 2004-10-25 at 15:56, Naeem Bari wrote:
>[color=green]
>> 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
>>
>>[/color]
>[/color]


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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

re: ON DELETE trigger blocks delete from my table

On 10/25/2004 3:47 PM, Tom Lane wrote:
[color=blue]
> "Naeem Bari" <naeem.bari@agilissystems.com> writes:[color=green]
>> 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;[/color]
>
> 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.[/color]

.... which would then again not work for the UPDATE case (not with the
same internal consequences though).


Jan
[color=blue]
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)[/color]


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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly