Re: How to keep the last row of a data set?

> -----Original Message-----
> From: Thomas Markus
> Sent: Thu, Dec 13 2012 23:14:21 CST
> To: seiliki(at)so-net(dot)net(dot)tw
> Subject: Re: [GENERAL] 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