Skip site navigation (1) Skip section navigation (2)

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

From: "Christopher Maier" <maier(at)med(dot)unc(dot)edu>
To: aklaver(at)comcast(dot)net
Cc: 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:55:11
Message-ID: 5D535C56-F811-4BAC-AF4A-EE6CF338AB63@med.unc.edu (view raw or flat)
Thread:
Lists: pgsql-sql
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


In response to

Responses

pgsql-sql by date

Next:From: Alvaro HerreraDate: 2008-10-10 18:05:26
Subject: Re: Problem with delete trigger: how to allow only triggersto delete a row?
Previous:From: Adrian KlaverDate: 2008-10-10 17:23:57
Subject: Re: Problem with delete trigger: how to allow only triggers to delete a row?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group