From: "Simon Kinsella" <simon@bluefiresystems.co.uk>
To: <pgsql-sql@postgresql.org>
Subject: Re: Help with trigger that updates a row prior to a potentially
	aborted deletion?
Date: Wed, 1 Mar 2006 20:41:20 -0000
Organization: Bluefire Systems Ltd
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcY9XVe+Y1zxFXApSleOlpLx49yNrgAErkkw
In-Reply-To: <20060301182345.GJ17356@phlogiston.dyndns.org>
Message-Id: <20060301204125.3A4B2199B1C@smtp04l.fasthosts.co.uk>

Ok thanks, will check this out. Is that the same as savepoints, or something
different? (am using 8.1.2)

At the moment I'm investigating using a rule (rewrite the DELETE as an
UPDATE to set the flag, then use an AFTER UPDATE trigger to attempt to
delete the row if the flag was set). Not sure if it's going to work but if
so I'll post back.

Thanks!

Simon

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Andrew Sullivan
Sent: Wednesday, March 01, 2006 6:24 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help with trigger that updates a row prior to a
potentially aborted deletion?

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq