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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | tuanhoanganh | 2008-02-23 02:54:54 | Re: Function parameters change when update to 8.3 |
Previous Message | Ralph Smith | 2008-02-23 01:57:17 | v7.4 pg_dump(all) need to encode from SQL_ASCII to UTF8 |