X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D813C9DC9FF for ; Wed, 1 Mar 2006 10:30:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 87716-10 for ; Wed, 1 Mar 2006 10:31:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id D875B9DC9B3 for ; Wed, 1 Mar 2006 10:30:56 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id BD95135EBD; Wed, 1 Mar 2006 06:30:54 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id BBA93359A3; Wed, 1 Mar 2006 06:30:54 -0800 (PST) Date: Wed, 1 Mar 2006 06:30:54 -0800 (PST) From: Stephan Szabo To: Simon Kinsella Cc: pgsql-sql@postgresql.org Subject: Re: Help with trigger that updates a row prior to a potentially In-Reply-To: <20060301133041.8EFFA19B797@smtp03l.fasthosts.co.uk> Message-ID: <20060301062525.H2973@megazone.bigpanda.com> References: <20060301133041.8EFFA19B797@smtp03l.fasthosts.co.uk> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.138 required=5 tests=[AWL=0.138] X-Spam-Score: 0.138 X-Spam-Level: X-Archive-Number: 200603/10 X-Sequence-Number: 24356 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.