-------------- Original message ----------------------
From: "Christopher Maier" <maier@med.unc.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
thisrow (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a
nonnullvalue 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
NULLvalue so the trigger completes, otherwise RETURN NULL to prevent the DELETE.
--
Adrian Klaver
aklaver@comcast.net
