From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Simon Kinsella <simon@bluefiresystems.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with trigger that updates a row prior to a potentially
Date: Wed, 1 Mar 2006 06:30:54 -0800 (PST)
Message-ID: <20060301062525.H2973@megazone.bigpanda.com> (raw)
In-Reply-To: <20060301133041.8EFFA19B797@smtp03l.fasthosts.co.uk>
References: <20060301133041.8EFFA19B797@smtp03l.fasthosts.co.uk>
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.
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: sszabo@megazone.bigpanda.com, simon@bluefiresystems.co.uk
Subject: Re: Help with trigger that updates a row prior to a potentially
In-Reply-To: <20060301062525.H2973@megazone.bigpanda.com>
* 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