seiliki@xxxxxxxxxxxxx writes: > I am trying to implement a mechanism that prohibits the last row of a data set from being deleted. > 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; The reason that doesn't work is you marked it "stable", so it always sees the starting state of the outer query. Mind you, even with that oversight fixed, this approach will do little to save you from concurrent-update situations. That is, transaction A could delete some of the rows with c1=1, and transaction B could concurrently delete the rest, and neither transaction will see a reason why it shouldn't commit. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
