How much of your database application should be in stored procedures?

Question

I'm writing a second interface to a database application to get around some shortcomings of the original interface. Unfortunately, if I create new records, expected audit trail records will not be generated. It sure would have been nice if the database design had worked such details into table triggers, or else provided a stored procedure API for such operations as inserting new records into these tables.

Should applications be designed this way, in general? How much of a database application should be in stored procedures?

Answer 1

Our teams have a rule - any data going into or out of a database must go through a stored procedure. We have even gone so far as to build restrictions into our data access components to enforce this.

As for other things such as book keeping, audit trails, etc, we put them in stored procedures as well. Triggers are convenient, but we found that for the purposes of audit logs involving the who, what, and when of an update, not all the data we want is redily available to the trigger. The only time we've ever used triggers is to keep a full change history of every record in a table, but even then, in hindsight, it caused maintenance problems and a sproc would have been better for us.