I am trying to implement a mechanism that prohibits the last row of a data set
from being deleted.
CREATE TABLE t1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2));
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
My desired effect:
Case 1, Permit this SQL to be executed:
DELETE FROM t1 WHERE c1=1 AND c2 <> 2;
This SQL keeps one row whose column c1 holds value "1". It does not hurt.
Case 2, Raise exception if users attempt to run this SQL:
DELETE FROM t1 WHERE c1=1;
This SQL attempts to delete all rows having value "1" in column c1. It must be
automatically aborted.
The following trigger protects nothing:
CREATE OR REPLACE FUNCTION tfd() RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE '%',(SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1);
IF (SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1) = 1 THEN
RAISE EXCEPTION 'Must keep at least 1 row for c1="%"',OLD.c1;
END IF;
RETURN OLD;
END $$ LANGUAGE PLPGSQL STABLE;
CREATE TRIGGER td BEFORE DELETE ON t1 FOR EACH ROW EXECUTE PROCEDURE tfd();
postgres@AMD64:/tmp$ psql -c 'DELETE FROM t1' test
Notice: 3
Notice: 3
Notice: 3
Notice: 3
Notice: 3
Notice: 3
DELETE 6
postgres@AMD64:/tmp$
Thank you in advance for helping me out!
Best Regards,
CN
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
