ON DELETE trigger blocks delete from my table

[Naeem Bari]

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

[Jan Wieck]

On 10/25/2004 2:56 PM, 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;
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
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. #
#================================================= = Ja******@Yahoo.com #

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

[Tom Lane]

"Naeem Bari" <na********@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Jan Wieck]

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. #
#================================================= = Ja******@Yahoo.com #

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

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

[Jan Wieck]

On 10/25/2004 3:47 PM, Tom Lane wrote:

"Naeem Bari" <na********@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.

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

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

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