Re: Help with trigger that updates a row prior to a potentially

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.