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
Message-ID: <20060301133041.8EFFA19B797@smtp03l.fasthosts.co.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: simon@bluefiresystems.co.uk
Subject: Re: Help with trigger that updates a row prior to a potentially aborted deletion?
In-Reply-To: <20060301133041.8EFFA19B797@smtp03l.fasthosts.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox