Delete trigger

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

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

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 "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.

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

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

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

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

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:

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.

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.

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