> -----Original Message----- > From: Thomas Markus > Sent: Thu, Dec 13 2012 23:14:21 CST > To: seiliki@xxxxxxxxxxxxx > Subject: Re: How to keep the last row of a data set? > > Hi, > > create an after delete trigger with > > IF (SELECT 1 FROM t1 limit 1) is null THEN > RAISE EXCEPTION 'Must keep at least 1 row'; > .... > > > hth > Thomas > AFTER DELETE trigger does not prevent all rows from being deleted, either: 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); CREATE OR REPLACE FUNCTION tfd() RETURNS TRIGGER AS $$ BEGIN RAISE NOTICE '%',(SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1); --IF NOT EXISTS (SELECT 1 FROM t1 WHERE c1=OLD.c1) THEN IF (SELECT 1 FROM t1 WHERE c1=OLD.c1 LIMIT 1) IS NULL THEN RAISE EXCEPTION 'Must keep at least 1 row for c1="%"',OLD.c1; END IF; RETURN OLD; END $$ LANGUAGE PLPGSQL STABLE; CREATE TRIGGER td AFTER DELETE ON t1 FOR EACH ROW EXECUTE PROCEDURE tfd(); test: DELETE FROM t1 WHERE c1=1; 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
