Thread: 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
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 
.
Anyway, setting the trigger AFTER DELETE works ok.
On Mon, 2004-10-25 at 15:56, Naeem Bari wrote:
.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
Attachment
"Naeem Bari" <naeem.bari@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
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. # #================================================== JanWieck@Yahoo.com #
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@Yahoo.com #
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@sss.pgh.pa.us]
Sent: Monday, October 25, 2004 2:48 PM
To: Naeem Bari
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] ON DELETE trigger blocks delete from my table
"Naeem Bari" <naeem.bari@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
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@Yahoo.com] Sent: Monday, October 25, 2004 2:52 PM To: Franco Bruno Borghesi Cc: Naeem Bari; pgsql-general@postgresql.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@Yahoo.com #
On 10/25/2004 3:47 PM, Tom Lane wrote: > "Naeem Bari" <naeem.bari@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 majordomo@postgresql.org) -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On 10/25/2004 3:53 PM, Naeem Bari wrote:
> 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/current/static/plpgsql-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
>
> Thanks for your help!
> naeem
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, October 25, 2004 2:48 PM
> To: Naeem Bari
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] ON DELETE trigger blocks delete from my table
>
> "Naeem Bari" <naeem.bari@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 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@Yahoo.com #
On Mon, 2004-10-25 at 15:09 -0500, Naeem Bari wrote: > 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@lfix.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
Interesting about the meta DDL. I wrote a very small language called QDL
for Query Description Language that uses the same idea. You feed QDL and
the SQL schema into the compiler and it writes C modules with embedded SQL.
Makes porting my application from one database to another a snap from the
application's perspective. It also makes embedded SQL easier to work with
than it normally is, but it's still less flexible than dynamic functions.
Embedded SQL is better than dynamic query building for safety critical full
path testing, and this method makes it more manageable.
Rick
"Naeem Bari"
<naeem.bari@agilissyste To: "Jan Wieck" <JanWieck@Yahoo.com>, "Franco Bruno
Borghesi"
ms.com> <franco@akyasociados.com.ar>
Sent by: cc: <pgsql-general@postgresql.org>
pgsql-general-owner@pos Subject: Re: [GENERAL] ON DELETE trigger blocks delete from my
table
tgresql.org
10/25/2004 03:09 PM
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@Yahoo.com]
Sent: Monday, October 25, 2004 2:52 PM
To: Franco Bruno Borghesi
Cc: Naeem Bari; pgsql-general@postgresql.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@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
naeem.bari@agilissystems.com ("Naeem Bari") writes:
> 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