> -----Original Message-----
> From: Thomas Markus
> Sent: Thu, Dec 13 2012 23:14:21 CST
> To: seiliki@so-net.net.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
