Automatically delete the parent object row when all the children are gone

Question

In PostgreSQL 8.3 database I have "bookings" table referencing "booking_transactions" table by ID. So that each booking belongs to a single transaction. It's possible to delete bookings from the database.

How can I make sure that a "booking_transactions" row is automatically deleted when all the "bookings" referencing it are gone?

I suspect an ON DELETE "bookings" trigger won't work, as it must query the other rows of the "bookings" table.

Answer 1

Trigger on "bookings" should work there's no problem to query the table itself in the trigger function.

Answer 2

The following plpgsql trigger function does the job

BEGIN
DELETE FROM
    booking_transactions bt
WHERE
    bt.id = OLD.transaction_id AND
    NOT EXISTS (SELECT 1 FROM bookings b WHERE b.transaction_id = bt.id);

RETURN OLD;
END;

It must be executed after bookings' DELETE