Postgres Pro
Downloads
Home   >   mailing lists

Thread: Delete trigger

Delete trigger

From
Leif Jensen
Date:
Hi,

   I am running PostgreSQL 9.3.4. I have a simple table with 3 fields:

CREATE TABLE devicegroup (
    groupid integer NOT NULL,
    ctrlid integer NOT NULL,
    userid integer NOT NULL
);
ALTER TABLE ONLY devicegroup
    ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid);

   I want to make sure that records are only deleted when all 3 fields are specified, so I tried make a trigger:

CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH STATEMENT
      EXECUTE PROCEDURE deleteUserDev();

which could check for NOT NULL on the 3 fields before actual doing the delete. Unfortunately this is not possible to do
FOREACH STATEMENT, and FOR EACH ROW would not give me the chance to check for NOT NULL. 

   Any ideas ?

 Leif

Re: Delete trigger

From
"Charles Clavadetscher"
Date:
Hello

Not sure I get it right, but all three fields are not nullable. So they will always have a value, which is what I
understandof "are specified". 
What do you need the trigger for in that case?

Bye
Charles

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Leif Jensen
> Sent: Freitag, 18. September 2015 10:23
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: [GENERAL] Delete trigger
>
>    Hi,
>
>    I am running PostgreSQL 9.3.4. I have a simple table with 3 fields:
>
> CREATE TABLE devicegroup (
>     groupid integer NOT NULL,
>     ctrlid integer NOT NULL,
>     userid integer NOT NULL
> );
> ALTER TABLE ONLY devicegroup
>     ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid);
>
>    I want to make sure that records are only deleted when all 3 fields are specified, so I tried make a trigger:
>
> CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH STATEMENT
>       EXECUTE PROCEDURE deleteUserDev();
>
> which could check for NOT NULL on the 3 fields before actual doing the delete. Unfortunately this is not possible to
> do FOR EACH STATEMENT, and FOR EACH ROW would not give me the chance to check for NOT NULL.
>
>    Any ideas ?
>
>  Leif
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Delete trigger

From
Leif Jensen
Date:
Hi Charles,

   If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete anything. I only want to delete if I do
"DELETEFROM devicegroup WHERE groupid=x AND ctrlid=y AND userid=z". I don't wanna let anyone delete more than 1 row at
atime. 

 Leif


----- Original Message -----
> Hello
>
> Not sure I get it right, but all three fields are not nullable. So they will
> always have a value, which is what I understand of "are specified".
> What do you need the trigger for in that case?
>
> Bye
> Charles
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Leif Jensen
> > Sent: Freitag, 18. September 2015 10:23
> > To: pgsql-general <pgsql-general@postgresql.org>
> > Subject: [GENERAL] Delete trigger
> >
> >    Hi,
> >
> >    I am running PostgreSQL 9.3.4. I have a simple table with 3 fields:
> >
> > CREATE TABLE devicegroup (
> >     groupid integer NOT NULL,
> >     ctrlid integer NOT NULL,
> >     userid integer NOT NULL
> > );
> > ALTER TABLE ONLY devicegroup
> >     ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid);
> >
> >    I want to make sure that records are only deleted when all 3 fields are
> >    specified, so I tried make a trigger:
> >
> > CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH
> > STATEMENT
> >       EXECUTE PROCEDURE deleteUserDev();
> >
> > which could check for NOT NULL on the 3 fields before actual doing the
> > delete. Unfortunately this is not possible to
> > do FOR EACH STATEMENT, and FOR EACH ROW would not give me the chance to
> > check for NOT NULL.
> >
> >    Any ideas ?
> >
> >  Leif
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>

Re: Delete trigger

From
Albe Laurenz
Date:
Leif Jensen wrote:
>    If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete anything. I only want to
> delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y AND userid=z". I don't wanna let
> anyone delete more than 1 row at a time.

I can't think of a way to do that with a trigger.

I'd write a
  FUNCTION delete_devicegroup(groupid integer, ctrlid integer, userid integer)
    RETURNS void CALLED ON NULL INPUT VOLATILE SECURITY DEFINER
that enables the user to delete a row and checks that all arguments
are NOT NULL.  The user doesn't get privileges to DELETE from the table directly.

Yours,
Laurenz Albe

Re: Delete trigger

From
Leif Jensen
Date:
Hello Laurenz,

   Thank you for you suggestion. I really want to aviod that someone 'accidentally' deletes too much by typing
(programming)a not full qualified DELETE ... statement. In your case one would have to always use the delete function,
butno restrictions on using the DELETE statement. 

 Leif


----- Original Message -----
> Leif Jensen wrote:
> >    If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete
> >    anything. I only want to
> > delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y AND
> > userid=z". I don't wanna let
> > anyone delete more than 1 row at a time.
>
> I can't think of a way to do that with a trigger.
>
> I'd write a
>   FUNCTION delete_devicegroup(groupid integer, ctrlid integer, userid
>   integer)
>     RETURNS void CALLED ON NULL INPUT VOLATILE SECURITY DEFINER
> that enables the user to delete a row and checks that all arguments
> are NOT NULL.  The user doesn't get privileges to DELETE from the table
> directly.
>
> Yours,
> Laurenz Albe
>
>

Re: Delete trigger

From
Ioana Danes
Date:
It depends on the size of the table and the frequency of updates, deletes but cold consider an audit table with triggers for update, delete and truncate. At least you have a way to recover deleted records.

Ioana


On Fri, Sep 18, 2015 at 5:52 AM, Leif Jensen <leif@crysberg.dk> wrote:
   Hello Laurenz,

   Thank you for you suggestion. I really want to aviod that someone 'accidentally' deletes too much by typing (programming) a not full qualified DELETE ... statement. In your case one would have to always use the delete function, but no restrictions on using the DELETE statement.

 Leif


----- Original Message -----
> Leif Jensen wrote:
> >    If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete
> >    anything. I only want to
> > delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y AND
> > userid=z". I don't wanna let
> > anyone delete more than 1 row at a time.
>
> I can't think of a way to do that with a trigger.
>
> I'd write a
>   FUNCTION delete_devicegroup(groupid integer, ctrlid integer, userid
>   integer)
>     RETURNS void CALLED ON NULL INPUT VOLATILE SECURITY DEFINER
> that enables the user to delete a row and checks that all arguments
> are NOT NULL.  The user doesn't get privileges to DELETE from the table
> directly.
>
> Yours,
> Laurenz Albe
>
>


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Delete trigger

From
"David G. Johnston"
Date:
On Friday, September 18, 2015, Leif Jensen <leif@crysberg.dk> wrote:
   Hello Laurenz,

   Thank you for you suggestion. I really want to aviod that someone 'accidentally' deletes too much by typing (programming) a not full qualified DELETE ... statement. In your case one would have to always use the delete function, but no restrictions on using the DELETE statement.


There is no way you can prevent a superuser from shooting themselves in the foot.  For anyone else you can enforce use of the function to perform the delete.

You could make a field called ok-to-delete and add a partial unique index on it so that only a single record can be marked ok to delete at a time and then have your trigger abort if it tries to delete a field without the ok to delete field set to true.

David J.
 

Re: Delete trigger

From
Leif Jensen
Date:
   Hi Ioana and David.

   Thank you. Yes, I can see the problem. I will look into your suggestions.

 Leif


----- Original Message -----
> On Friday, September 18, 2015, Leif Jensen <leif@crysberg.dk> wrote:
>
> >    Hello Laurenz,
> >
> >    Thank you for you suggestion. I really want to aviod that someone
> > 'accidentally' deletes too much by typing (programming) a not full
> > qualified DELETE ... statement. In your case one would have to always use
> > the delete function, but no restrictions on using the DELETE statement.
> >
> >
> There is no way you can prevent a superuser from shooting themselves in the
> foot.  For anyone else you can enforce use of the function to perform the
> delete.
>
> You could make a field called ok-to-delete and add a partial unique index
> on it so that only a single record can be marked ok to delete at a time and
> then have your trigger abort if it tries to delete a field without the ok
> to delete field set to true.
>
> David J.
>