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

From: "Christopher Maier" <maier(at)med(dot)unc(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: Problem with delete trigger: how to allow only triggers to delete a row?
Date: 2008-10-10 16:08:31
Message-ID: 6C9459A9-55A7-4907-B84F-34472F8B56B6@med.unc.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Adrian Klaver 2008-10-10 17:23:57 Re: Problem with delete trigger: how to allow only triggers to delete a row?
Previous Message Chris Browne 2008-10-10 14:59:14 Re: replicating a table in several databases