Skip site navigation (1) Skip section navigation (2)

Cascading Trigger - changing row on delete does not delete row

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: Postgres-General <pgsql-general(at)postgresql(dot)org>
Subject: Cascading Trigger - changing row on delete does not delete row
Date: 2008-02-23 02:36:26
Message-ID: 47BF86AA.6070004@lorenso.com (view raw or flat)
Thread:
Lists: pgsql-general
All,

I have 2 tables which both have triggers on them.  When I delete a row 
on table A, a cascading trigger ends up modifying rows in table B.  The 
modified rows in table B trigger an update on rows in table A which 
happens to be the same row that I am trying to delete.

I don't get any errors from the delete, yet PostgreSQL tells me 0 rows 
affected by the delete and sure enough the row I just tried to delete is 
still there.  Running the delete a 2nd time works because the trigger 
does not cascade and effect the deleted row.

Is there a way to know that a row I am deleting is being deleted so I 
don't update it?

I thought about adding a boolean column 'is_being_deleted' but I can't 
set that to true without updating the row (which I'm trying to avoid).

I've thought about using PL/Perl to access transaction-level global 
variables where I could store the ID of the row I'm deleting and fetch 
that value in order to avoid it in my updates ... but I don't want 
invoke the PL/Perl interpreter and slow down what I'm already doing in 
PL/PGSQL.  Are there transaction-level variables in PL/PGSQL (globals)?

Suggestions?

-- Dante



pgsql-general by date

Next:From: tuanhoanganhDate: 2008-02-23 02:54:54
Subject: Re: Function parameters change when update to 8.3
Previous:From: Ralph SmithDate: 2008-02-23 01:57:17
Subject: v7.4 pg_dump(all) need to encode from SQL_ASCII to UTF8

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group