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
raiseexception 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
