From: | "Naeem Bari" <naeem(dot)bari(at)agilissystems(dot)com> |
---|---|
To: | "Jan Wieck" <JanWieck(at)Yahoo(dot)com>, "Franco Bruno Borghesi" <franco(at)akyasociados(dot)com(dot)ar> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: ON DELETE trigger blocks delete from my table |
Date: | 2004-10-25 20:09:30 |
Message-ID: | 53F35087CC531844AD19CCAE6FA66929368B@util.agilissystems.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 #
From | Date | Subject | |
---|---|---|---|
Next Message | Edmund Bacon | 2004-10-25 20:15:55 | Re: ON DELETE trigger blocks delete from my table |
Previous Message | Naeem Bari | 2004-10-25 19:53:25 | Re: ON DELETE trigger blocks delete from my table |