Re: ON DELETE trigger blocks delete from my table
От | Jan Wieck |
---|---|
Тема | Re: ON DELETE trigger blocks delete from my table |
Дата | |
Msg-id | 417D61A8.9030600@Yahoo.com обсуждение исходный текст |
Ответ на | Re: ON DELETE trigger blocks delete from my table ("Naeem Bari" <naeem.bari@agilissystems.com>) |
Список | pgsql-general |
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 #
В списке pgsql-general по дате отправления: