A common example is making sure that every event in a bookkeeping system balances to zero (e.g. three rows: -125 EUR bank, +100 EUR office materials, +25 EUR tax). To enforce this at the database level you either need to run at SERIALIZABLE, take a table lock, or do something horrible with your database structure (like putting all rows of the event in a JSON blob).
Or you deny direct table inserts, and provide a stored procedure for inserting multiple rows in one transaction. But I see how it could be useful in the generic case.
Which is exactly what I did when I had to solve the problem. :) And you are right in mentioning that the fact that you can restrict modification of the database to only certain stored procedures is a great help here.
Or you deny direct table inserts, and provide a stored procedure for inserting multiple rows in one transaction. But I see how it could be useful in the generic case.