I have a "master-detail" kind of situation, as illustrated here:
CREATE TABLE master(id SERIAL PRIMARY KEY,foo TEXT
);
CREATE TABLE detail(id SERIAL PRIMARY KEYmaster BIGINT NOT NULL REFERENCES master(id),bar TEXT
);
(this is a simplification, of course)
I would like a way to restrict the addition of new detail records, but
only after the initial detail records have been inserted into the
system. Ideally, I'd like to start a transaction, enter the master
record and its detail records, then commit the transaction, which
would then lock these records so no more detail records can ever be
added. As a concrete example, you might think of the master record as
an Order, while the details are Line Items for an order. When
initially creating the Order, I'd like to add as many Line Items as
necessary, but once the Order is entered, I don't want anybody to be
able to add more Line Items.
Is there a way to do this straightforwardly? It seems like I might be
able to use a BEFORE INSERT ROW trigger on the detail table to see if
there are already any detail records for the master row. If there are
no detail records, then I can go ahead and add. If there are detail
records, then maybe I could use a pl/perl hash to carry information
around as to whether or not those records were added in the current
transaction, and then decide whether to insert based on that. That
seems rather cumbersome and baroque, though.
Thanks in advance for any suggestions,
Chris
