I have some pretty extreme experience with this (email me for details).

The upshot is that business logic in stored procedures should only be done when absolutely required. Triggers are horrible as well.

You may say to yourself, "but Scott, it's just this simple little thing, and triggers make it so much easier".

That way lies madness. Trust me.

Also, as soon as you start putting logic in databases your scalability is almost immediately limited to vertical scaling, unless you are very very careful. Most developers are not that careful.

Before I start, some background: I've written entire rules-based automated travel booking systems in stored procedures. I've worked at places that have over a million lines of PL/SQL code. The very forums we're using incorporate stored procedures to manage the complex insertion logic of a new post using nested set trees. I'm not trying to pull argument from authority, but just understand that I've seen a few patterns and anti-patterns, and I've done things with databases that would give Bryce wet dreams for a month.

Also, a caveat: I work on big, complex systems. Some of the things I'm going to mention may not apply to smaller systems. However, every system starts small. It doesn't take a lot of effort to future-proof while it's small and if you do you may reap a lot of benefit later on, or indeed be able to do things that would be nearly impossible from an organizational or resource standpoint once the system gets big.

There are probably even more arguments for my position but I'm not spending a lot of time on this, believe it or not:

1) Maintenance
2) Traceability
3) Accessibility
4) Testing
5) Scalability
6) Portability
7) Functionality

Maintenance: Presumably you are using a different language, such as PHP, to do what you're calling 3rd and 4th order business logic. As soon as you add a second language you're multiplying your maintenance costs. You need a new test framework, you need to add more functionality to your build system, you need resources that can program in PL/SQL or TransactSQL or whatever, you need more debuggers and tools, and so on. Multiple programming environments increase your technical and cognitive surface.

Traceability: Where is the logic executing? Because you've added a whole new environment that can run logic, but not *all* the logic -- remember that 3rd and 4th order logic is somewhere else -- you don't immediately know where a particular piece of logic may be located. Let's say you have that bit that calculates "the price for a product based on cost, overhead, contract price, etc." in a stored procedure. Now, one of your clients requires that you calculate their price based on the results of a web service call. You had better not be trying to do that from within a stored procedure (I will kill you for every time you mention UTL_HTTP, fair warning), so now you need to move that logic out of the stored procedure and into your app tier. Maybe you have other logic for other clients as well... do you move everyone's out now? Or do you maintain price calculation logic in two different places? The next time you need to make a change, how do you know the price calculation logic is in PHP, and the state tax logic is still in a stored procedure? Or what if the pricing logic needs to look up values, or it now needs to be modifiable by an administrative person or user? If you've got logic in a trigger that is accessing other tables you're asking for trouble. Because triggers are essentially invisible you can end up with chains of them doing multiple things, all within your transaction space. How do you really know what's happening on that insert? Another variation on this point is separation of concerns: when you start using stored procedures and triggers the line between data and logic becomes blurred.

Accessibility: Some databases, like Oracle, let you do an awful lot in their stored procedures. Invariably this functionality is slower, clunkier, and almost guaranteed to cause performance issues as the system grows. Or take logging. You're going to want to have logging in any procedural code you write. I've written a syslog client for PL/SQL just to get a logging solution that would perform well enough to not cause the database heartburn under load. Not recommended.

Testing: Unit tests need to be fast. Very fast. They should run every time you check code into your repository, and they need to be self-contained. If you have logic in a database system, now you need to have a running database to run your tests, which means that tests for this logic automatically get moved to the component (integration) testing phase of your development pipeline. Errors get caught later as a result. And since your code resides in a trigger, you have to scrub your data before and after testing, all of which complicates your test harness.

Scalability: Once you dip into a database's transactional context your costs of execution go up by an order of magnitude at least. If you can do your calculations, constraints, validations, etc. before you have to invoke a remote call with a transaction context and persistence, you're going to scale better. Leave that logic out in the app tier and it can be parallelized, optimized. A database is persistent storage. Treat it like a hard drive, and don't go there unless you absolutely have to. The most performant systems do their work in memory (this can be done in a high availability fashion if required) and write the persistence out lazily.

Portability: IWETHEY is a case in point. I'd love to test it on MySQL, but I can't because the post creation logic is implemented in a stored procedure. If it were out in my app tier then switching to a different persistent store would be as simple as changing my JDBC driver and loading the table schema elsewhere. Or let's say that you decide a few years into the life of the application that parts of it would be better off in a columnar-oriented database like Vertica. Or maybe in something completely different like BigTable. If you are only persisting data with your persistent data store, you can easily swap that piece out.

Functionality: Rules engines. Put your validation, calculation, and constraint logic in a rules engine and you can improve your functionality (and maintenance). Your admins can change the rules without needing a programmer, either directly via the rules themselves or with an administrative GUI. Or not... either way the functionality available to a general purpose language, running outside of a database's execution context, is much greater than the limitations imposed by a special purpose stored procedure language.

Paging, sorting, and filtering are what relational databases are there for in general, beyond simple persistent storage. Because you're using a portable API (SQL), the actual code controlling what the sort is, what the filter is, etc. is kept out in the app tier so most if not all of my issues with business logic don't apply.

There's an issue with scaling if you're not using read-only shards. Depending on the use cases you'll also want to investigate caching.

Also depending on the use cases (do they involve extremely complex filtering or reduction logic) you might be better off moving out of the database. The key is to keep it modularized to watch your separation of concerns.

Until you have some database code that, for every call, creates TCP sockets, makes external procedure calls (socket to process to C library, woo), builds up and tears down all of its package state (DBMS_SESSION.RESET_PACKAGE), logs megabytes of text per second, simulates synchronous calls using AQ with a 30 second timeout, stores data using comma delimited strings, XML, and UDTs, and generates HTML using string catenation in PLSQL... UNTIL then no, no you do not actually "win".

And no comments, natch.