I am new to Postgresql, and although it would be my first choice of
database, I am trying to see what sort of complexities await me
depending on which route I go down. Though I have read as much as I can,
I am still stuck on knowing how I will approach the enforcement some
fundamental rules on my data at the db server level (to protect me from
my own application coding as much as anything!), that involve more than
one table and/or more than one row.
Simplified table structure:
CREATE TABLE period (id integer PRIMARY KEY, is_closed boolean);
CREATE TABLE tx_header (id integer PRIMARY KEY, period_id integer
REFERENCES period(id));
CREATE TABLE tx_detail (id integer PRIMARY KEY, tx_header_id integer
REFERENCES tx_header(id), account_id integer, amount numeric(10,2));
Here are some of the restrictions I want to impose:
1. No inserts into tx_header if period.is_closed is TRUE for the
proposed new records
2. A tx_header record must have some related tx_detail records
3. Inserts into the tx_detail must *only* happen simultaneously with a
tx_header insert
4. The sum of tx_detail.amount over any tx_header_id *must always equal
zero*
5. No updates in tx_header or tx_detail *ever*
6. Deletes in tx_header and tx_detail are allowed but only via specific
stored procedures (with built-in conditions that are so cautious and
numerous I'm not going into them here!)
Some or all of this may be suited to triggers - e.g. I think I can see
how to implement restriction 1 - but I can't see how to do restriction
4, because I can't seem to get at the sum of tx_detail.amount in a
statement level trigger.
Alternativley should I go for an approach like my restriction 6, and
write stored procedures which impose all the necessary restrictions to
do all data manipulation on these tables. How would I then prevent any
changes being made by other means?
Any advice gratefully received!