trigger/refint question..

Hi.

Im trying to implement a referential integrity check that I suspect there
is a much better way to do that what I have done. Basically what I have is
a "order" table and a "order items" table. I would like to make it so that
if all items are removed from the order, then the corresponding "order"
entry is removed.

e.g.:

CREATE TABLE orders (
id SERIAL,
dname TEXT NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE order_items (
item_id SERIAL,
order_id INT NOT NULL,
item VARCHAR(80) NOT NULL,
PRIMARY KEY (item_id)
);

BEGIN;
INSERT INTO orders (dname) VALUES ('FOO');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'FOO ITEM #1');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'FOO ITEM #2');

INSERT INTO orders (dname) VALUES ('BAR');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'BAR ITEM #1');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'BAR ITEM #2');
COMMIT;

So we have 2 orders, each with 2 items in it. Suppose someone later comes
along and deletes all of the items in the order:

DELETE FROM order_items
WHERE order_id=1;

Ideally, I would like a trigger (or something similar) to fire after this
delete runs that does something like:

DELETE FROM orders
WHERE id IN (
SELECT id
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM order_items WHERE order_id=o.id)
);

(sort of the invers of "FOREIGN KEY (...) REFERENCES (..) ON DELETE CASCADE").

The trick seem sto be getting this into a function somehow. The delete
query does not return a value, so I am not sure how to go about doing that.

Assuming I *could* get this into an sql or plpgsql function somehow, I could
simply do:

CREATE TRIGGER tr_order_items_del AFTER DELETE ON order_items
FOR EACH ROW EXECUTE PROCEDURE del_order_items();

And I think that would solve my problem.

Am I making this overly complicated? Is there an easier way? If not,
then does anyone have any ideas how I can make this work?

Thanks.

Mike