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
Message-ID: <20060301204125.3A4B2199B1C@smtp04l.fasthosts.co.uk> (raw)
In-Reply-To: <20060301182345.GJ17356@phlogiston.dyndns.org>
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
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: <20060301204125.3A4B2199B1C@smtp04l.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