Yes I originally started working on a function based approach like you
suggest, but realised it wouldn't cover the situation where the delete
operation is fired as a result of a CASCADE ON DELETE constraint from a
parent table, rather than as a manual query. I suppose I could ditch that
particular cascading contraint and replace it with a trigger function that
calls my custom delete function. Not sure if I like that though ;)
Thanks for your feedback,
Simon Kinsella
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Wednesday, March 01, 2006 2:31 PM
To: Simon Kinsella
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help with trigger that updates a row prior to a
potentially aborted deletion?
On Wed, 1 Mar 2006, Simon Kinsella wrote:
> Hi all,
>
> I have a situation where a DELETE operation may (correctly) fail due
> to a RESTRICT FK constraint. If so, I need to set a flag in the row
> indicating that it has been marked for deletion so that I can
> disregarded in subsequent queries.
>
> I'm trying to achieve this with a BEFORE DELETE trigger, which would
> set the 'marked_for_deletion' field to TRUE before attempting the delete
proper.
> Then if the DELETE fails the row would still be tagged and I'd be happy.
> Problem is, when the DELETE operation fails with a RESTRICT VIOLATION
> error the entire operation is rolled back, including the BEFORE
> triggers, leaving me back where I started.
>
> Is there anyway to get the DELETE operation, or more specifically the
> FK constraint, to fail silently, i.e. to skip over the failed
> operation and not throw an exception?
Not with the standard constraint trigger, no. In general, constraint checks
happen after the action and as such can't skip over an operation since it's
already happened.
You might be able to do this within a function however if you do the update
and then start an exeption checking block to do the delete.