Date: Wed, 1 Mar 2006 13:23:45 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Help with trigger that updates a row prior to a potentially
	aborted deletion?
Message-ID: <20060301182345.GJ17356@phlogiston.dyndns.org>
References: <20060301133041.8EFFA19B797@smtp03l.fasthosts.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060301133041.8EFFA19B797@smtp03l.fasthosts.co.uk>
User-Agent: Mutt/1.5.9i

On Wed, Mar 01, 2006 at 01:30:23PM -0000, Simon Kinsella wrote:
> 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.

Yes.  In 8.1, you could use a subtransaction for the DELETE, which I
think would allow you to rollback at that point and still leave the
UPDATE in place.

A


-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
		--Philip Greenspun