Re: ON DELETE trigger blocks delete from my table - Mailing list pgsql-general
From | Naeem Bari |
---|---|
Subject | Re: ON DELETE trigger blocks delete from my table |
Date | |
Msg-id | 53F35087CC531844AD19CCAE6FA66929368B@util.agilissystems.com Whole thread Raw |
In response to | ON DELETE trigger blocks delete from my table ("Naeem Bari" <naeem.bari@agilissystems.com>) |
Responses |
Re: ON DELETE trigger blocks delete from my table
(Oliver Elphick <olly@lfix.co.uk>)
|
List | 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@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 #
pgsql-general by date: