Re: ON DELETE trigger blocks delete from my table

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(dot)bari(at)agilissyste To: "Jan Wieck" <JanWieck(at)Yahoo(dot)com>, "Franco Bruno Borghesi"
ms.com> <franco(at)akyasociados(dot)com(dot)ar>
Sent by: cc: <pgsql-general(at)postgresql(dot)org>
pgsql-general-owner(at)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(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 #

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