Re: Problem with delete trigger: how to allow only triggers to delete a row?

From: aklaver(at)comcast(dot)net (Adrian Klaver)
To: "Christopher Maier" <maier(at)med(dot)unc(dot)edu>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Problem with delete trigger: how to allow only triggers to delete a row?
Date: 2008-10-10 17:23:57
Message-ID: 101020081723.18944.48EF8FAD0009F9D900004A0022058864429D0A900E04050E@comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

-------------- Original message ----------------------
From: "Christopher Maier" <maier(at)med(dot)unc(dot)edu>
> I have a table where some rows are originally entered, and others are
> logically deduced from original rows by triggers. For instance, if
> one row contains data indicating that "A < B" and another row
> indicates "B < C", the triggers will generate a new row indicating
> that "A < C". All deduced rows have a boolean attribute (named
> "deduced") that is TRUE only if the row was generated by such a
> deduction. A value of FALSE indicates that the row was original data,
> entered by a user.
>
> When original data is modified, the triggers are responsible for
> removing any deduced rows that are now invalid and generating new rows
> that are now implied. I would like to make it so that the only way
> that deduced rows can be deleted is through the actions of these
> triggers; I don't want a user inadvertently deleting a deduction when
> the underlying premises (the original rows that were used to generate
> the deduced rows) still imply that deduction is valid. Users should
> only be able to manipulate the original data.
>
> I can create a trigger that will prevent deletion of deduced rows
> easily enough, but I'm not sure how to let rows targeted for deletion
> by these deduction triggers through. Is there a way to pass some sort
> of state indicator into a trigger? Is this at all possible?
>
> Thanks in advance,
> Chris
>
>
> --

From the manual http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html:

Row-level triggers fired BEFORE may return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value

Could you have the the trigger examine the row to see if it meets the criteria for deletion. If it does RETURN a NON NULL value so the trigger completes, otherwise RETURN NULL to prevent the DELETE.

--
Adrian Klaver
aklaver(at)comcast(dot)net

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Christopher Maier 2008-10-10 17:55:11 Re: Problem with delete trigger: how to allow only triggers to delete a row?
Previous Message Christopher Maier 2008-10-10 16:08:31 Problem with delete trigger: how to allow only triggers to delete a row?