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

On Oct 10, 2008, at 1:23 PM, Adrian Klaver wrote:
> 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.

Thanks for your reply, Adrian. This is indeed part of the solution.
My problem concerns the determination of the criteria for deletion.
Each row has a boolean attribute that says whether it is deduced or
not and that can be inspected readily enough. However, I need to
restrict deletion based on, for lack of a better term, "where" the
DELETE command comes from. I do not want someone sitting at a psql
console to be able to type:

DELETE FROM my_table WHERE deduced IS TRUE;

This should fail because users should only be able to delete non-
deduced rows. However, when a user deletes a non-deduced row, my
triggers issue DELETE commands for all deduced rows that are logically
derived from that non-deduced row. Back to the example from my
original post, if I have two rows in the table that say, in effect:

A < B
B < C

then the triggers will generate a row that says "A < C". If the user
then deletes the "B < C" row, the triggers will delete the "A < C"
row, because there is no longer any support for this. I need to
figure out how to block DELETEs from the user, while allowing DELETEs
that come from the triggers. If I could pass along some kind of flag
or parameter with the DELETE commands issued from the triggers, then
that might do it, but my understanding of trigger parameters is that
they are the same for all invocations on a particular table, whereas I
need them to be different for each call of the trigger.

I'm currently using plpgsql, but I'm open to other PL languages or
architectural restructurings if that's necessary. I'm just stumped as
to how to achieve this effect.

I hope that clarifies things.

Thanks again,

Chris