From: "Simon Kinsella" <simon@bluefiresystems.co.uk>
To: <pgsql-sql@postgresql.org>
Subject: Help with trigger that updates a row prior to a potentially aborted
	deletion?
Date: Wed, 1 Mar 2006 13:30:23 -0000
Organization: Bluefire Systems Ltd
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcY9NEiaeONP9DRKRgippiVmEcXsog==
Message-Id: <20060301133041.8EFFA19B797@smtp03l.fasthosts.co.uk>

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? I'm really racking my brains on this one but not really
getting anywhere!

I have sample data defs to play with if that would be helpful.

Thanks in advance!

Simon Kinsella