ON DELETE trigger blocks delete from my table

#1

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

#2

"Naeem Bari" <naeem.bari (AT) agilissystems (DOT) com> writes:

Quote:

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 majordomo (AT) postgresql (DOT) org)

#3

On 10/25/2004 2:56 PM, Naeem Bari wrote:

Quote:

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

Quote:

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 #

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

#4

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

Quote:

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

Quote:

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 #

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

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

#5

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.pa.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.bari (AT) agilissystems (DOT) com> writes:

Quote:

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 5: Have you checked our extensive FAQ?

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

#6

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:

Quote:

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

Quote:

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 #

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#7

naeem.bari (AT) agilissystems (DOT) com ("Naeem Bari") writes:

Quote:

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...

Yes, plpgsql sets a variable TG_OP to INSERT, UPDATE or DELETE.

so, for example

IF ( TG_OP = ''DELETE'' ) THEN
RETURN old;
ELSE
RETURN new;
END IF;

--
Remove -42 for email

#8

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

Quote:

"Naeem Bari" <naeem.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.

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

Jan

Quote:

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 (AT) postgresql (DOT) org)

--
#================================================= =====================#
# 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 #

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

#9

On 10/25/2004 3:53 PM, Naeem Bari wrote:

Quote:

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?

The variable TG_OP contains a string of 'INSERT', 'UPDATE' or 'DELETE'
as per the documentation:

http://www.postgresql.org/docs/curre...l-trigger.html

Making it an AFTER trigger still ensures that the transaction is rolled
back if the trigger fails. What it also ensures is that no trigger fired
later can modify the NEW row after your auditing already took place. As
your trigger is, this doesn't matter to you. But as soon as you include
some new value in your auditing table you might be surprised not to find
that new value in the row.

In PostgreSQL a BEFORE trigger procedure on INSERT or UPDATE can modify
values in NEW because it is called BEFORE the new row is actually stored
in the table. This is usefull for enforcing timestamps, usernames,
derived values, you name it. AFTER triggers can't do that and are
guaranteed to see the values that really have been stored.

Jan

Quote:

Thanks for your help!
naeem

-----Original Message-----
From: Tom Lane [mailto:tgl (AT) sss (DOT) pgh.pa.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.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

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

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

--
#================================================= =====================#
# 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 #

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#10

On Mon, 2004-10-25 at 15:09 -0500, Naeem Bari wrote:

Quote:

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?

CREATE OR REPLACE FUNCTION ...

--
Oliver Elphick olly (AT) lfix (DOT) co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Only take heed to thyself, and keep thy soul
diligently, lest thou forget the things which thine
eyes have seen, and lest they depart from thy heart
all the days of thy life; but teach them to thy sons,
and to thy sons' sons..." Deuteronomy 4:9

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)