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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 | [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
