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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
