Hi! I am trying to implement a mechanism to reserve the last row for every distinct value in column "c1". CREATE TABLE table1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2)); CREATE OR REPLACE FUNCTION keep1() RETURNS TRIGGER AS $$ BEGIN IF (SELECT COUNT(*) FROM table1 WHERE c1=OLD.c1) = 1 THEN RAISE EXCEPTION 'The last row for c1 = % must be kept!',OLD.c1; END IF; RETURN OLD; END $$ LANGUAGE PLPGSQL STABLE; CREATE TRIGGER test BEFORE DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE keep1(); INSERT INTO table1 VALUES (1,1),(1,2),(1,3); With above rows inserted, my desired effect follows: (1) allowed user operations: DELETE FROM table1 WHERE c2 IN (1,2); /* Row (1,3) is still kept */ DELETE FROM table1 WHERE c2=2; DELETE FROM table1 WHERE c2=3; /* Row (1,1) is still kept */ DELETE FROM table1 WHERE c1=9; /* We have at least one row with c1=1 that is kept intact */ (2) disallowed user operations: DELETE FROM table1 WHERE c1=1; /* Exception wanted. Every row for c1=1 would be deleted otherwise. */ DELETE FROM table1 WHERE c2 IN (1,2); DELETE FROM table1 WHERE c2=3; /* The second DELETE must raise exception. */ The above trigger: (1) It raises exception if table1 has only one row (1,1) and I delete it. This gives expected effect. (2) When table1 contains 3 rows (1,1),(1,2),(1,3), then all of the following SQL yields unwanted result - they do not raise exception and I can not figure out why the trigger is silenced: DELETE FROM table1; DELETE FROM table1 WHERE c1=1; DELETE FROM table1 WHERE c2 IN (1,2,3); Would someone please provide me some idea for a working implementation? Thank you in advance! CN -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
