Data integrity is orthogonal to stored procedures and triggers

Depends on how you define data integrity. If I have a banking application where it should be impossible to update the value of the balance column of the current_account table without there being an appropriate entry in the debit or credit tables I would call it a data integrity issue, and enforcing it with stored procedures and/or triggers would seem a sensible option.

The table doesn't have a behavior that is associated with it. If anything, the behavior is associated with the action that triggers it, not the table the action was performed on.

I think I'll just have to say to-may-to / to-mah-to on this one. As far as I'm concerned when I do something like:

CREATE OR REPLACE TRIGGER enforce_business_rules
  AFTER UPDATE
  ON current_account
  ... etc ...
[download]

I'm doing something to the current_account table. YMMV :-)

Triggers do not have to affect the table that they trigger upon. For example, we have a trigger that will add rows to other tables when the given table is inserted into. That has nothing to do with data integrity and everything to do with business logic. (You even named your trigger accordingly.)

In my opinion, you are confusing business rules and data integrity. Data integrity is a set of rules about the form of the data, based on how the data moves. Business rules have to do with things like you discuss. Credits, debits, and balances are a business rule. Data integrity would require that every credit and every debit have a valid FK to the current_account table. Whether or not they balance is an application issue.

This is actually a pet peeve of mine. Putting business rules into the datastore limits your options. You end up not being able to do the following:

• change datastores (Oracle -> MySQL, for example)
• safely upgrade versions of your datastore. (Oracle, for example, has had issues between point releases of 9i.)
• support more than one type of datastore (should this be needed)
• allow more than one application to use your datastore
Not to mention that it's a huge maintenance headache, having to support more than one language and/or needing to have a DBA do development.

That last item may be a bit confusing, so I'll explain the reasoning behind it. Most databases begin life supporting one application, so there's a bit of myopia involved in the design, especially of the schema and supporting triggers / stored procedures. Some places put a lot of the business rules into the database.

So, let's say you do that and your database has been very successful. Now, your manager / VP / whomever wants you to add another application. Say, a reporting application for the clients, over the Web. Not a problem. But, now they want you to add the capability to have the clients update the data. Oops!

You now need to scour every single bit of code within the database to make sure that seemingly simple data changes that the web application wants to make won't adversely affect your database because every time you made that data change within the internal app, you always wanted to do XYZ as well. It's much simpler to have a webservices-type design with N tiers. That way, each tier does one thing, one thing only, and does that one thing well.

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

I shouldn't have to say this, but any code, unless otherwise stated, is untested

Triggers do not have to affect the table that they trigger upon. For example, we have a trigger that will add rows to other tables when the given table is inserted into.

True, but I'm afraid I don't understand the point that you're trying to make.

In my opinion, you are confusing business rules and data integrity. Data integrity is a set of rules about the form of the data, based on how the data moves. Business rules have to do with things like you discuss.

I've failing to see the clear distinction that you appear to be drawing between business rules and data integrity. I've certainly been using the latter term in the sort of contexts I discussed previously for over ten years with nobody apparently getting confused ;-)

This is actually a pet peeve of mine. Putting business rules into the datastore limits your options.

I agree. Putting business logic in the database layer is often a mistake. The most common problem I see is splitting the same 'layer' of business logic between the database and non-database code, which gets you the worse of both worlds.

That said, you fail to list the advantages. Putting at least the basic business rules in the database allow you to present the relation model to the application and be certain that a poorly coded application isn't going to foul up your data. Database-side logic can also be a lot faster since you don't have to throw stuff over the wire.

While I tend to keep my business logic out of the database (because most of the projects I work on aren't of a sufficient scale to make it useful) it comes down to the project and the team. There are no hard and fast rules either way.

You now need to scour every single bit of code within the database to make sure that seemingly simple data changes that the web application wants to make won't adversely affect your database because every time you made that data change within the internal app, you always wanted to do XYZ as well. It's much simpler to have a webservices-type design with N tiers. That way, each tier does one thing, one thing only, and does that one thing well.

I don't understand the point you are making here.

Triggers are often used for data integrity to enforce 1 to 1 and 1 to 0 or 1 relationships between more than 2 tables.
--Solo

--
You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.