A sincere thank you for the reply.
Respectfully, I believe you're going down a bad road. For a small application, and a developer who isn't asleep at the wheel, what you're suggesting can work OK; I imagine it hasn't been a problem for you.
Generally, for everything else, it tends to blow up down the road. And even for smaller projects, the benefits of keeping your logic out of the DB (faster, cleaner, clearer, easier to learn, easier to update) are significant.
I sincerely want to make an effort to be useful to folks who may not have worked with larger projects before. Please take this post in that spirit. It's a long, and I hope informative, post.
Make your code's intent clear ... alternatively, write logic in the right context
The biggest problem is code intent and flow control. Databases model data, GUIs model user actions, but neither necessarily model business logic. You've got business logic code for that -- like your permissions handling.
Code has a logic-centric view. Databases have a data-centric view. If you write logic in the DB, you're abstracting what you're writing from what you're actually trying to accomplish.
Error handling is a great example for flow control. Trapping an error in the DB, correctly identifying the problem and bubbling it up in a user-appropriate way in a clean fashion, as you said, is difficult.
Keep your code together
Another big problem is finding all the right code. For a newbie or a dev who hasn't looked at a bit of code in 6 months, this is very important.
Breaking your code up into different buckets based on what data it modifies makes your logic - your flow - hard to follow for anyone who isn't intimately familiar with the project.
Consider two implementations for a feature:
1) Application code triggers an update to three tables. A bunch of magic spread across several triggers on those three different tables and written in (depending on your implementation) three different schema files, or one huge file with your entire schema, updates three additional tables. E.G.: "most complex updates are performed using triggers and rules"
2) One function in one file that, in a transaction, updates six tables.
Which is easier to follow? From which implementation can you get a clear vision of the intent of the code? Which is easier to skim? If a new developer looks at your code, in which implementation is he more likely to miss something the feature touches?
Don't make interoperability harder
If you push logic into your DB, interoperability becomes harder, not easier. Before, you had just data, and two different applications could use it differently and have different requirements -- as long as what they did was consistent with the data model (your schema).
Now, with logic in the DB, anyone wanting to share your data has to work around your triggers, or co-opt them, or write entirely new triggers, procedures, and generally muck up your application to accomplish their goals.
Keep your platform flexible
Pushing logic into the DB, you may have made it easier to switch languages (although you still have a bunch of "front end" code you'd have to convert/maintain), but now it is much harder to switch databases. What was once merely data is now jumbled up with your application. If Oracle, MSSQL, etc. becomes a necessity, you're not just switching databases, you'll likely be refactoring significant chunks of your app.
Most business needs won't demand you switch languages (worst case, cross-compile or use IPC). Some business needs will demand you switch databases -- my company is now porting our app to MSSQL.
Your database is likely your bottleneck
In my experience, the first serious scalability problems most apps encounter are in the database. And the solution is always "more hardware." Beefier machines.
Then comes horizontal scaling ("moar servers plz"), which is almost always easier to do with your application than your database.
Why add more to your DB load? Write your logic in your application and run your DB on a dedicated machine. Then you're free to implement caching , an option you don't have with your logic in the DB. Then if you have scaling problems, buy beefier hardware and eventually, in big implementations, scale horizontally.
Don't complicate updates and debugging
Multiple instances of our application server can be run against the same database. This makes testing code changes or debugging a very complicated production issue possible without modifying the application in production.
When your app is built into your DB, inserting debug statements amounts to modifying production. Running another instance becomes very challenging (spare replicated server?) if not impossible.
--
A lot of people have had success implementing their applications like you've suggested. Our parent company did... and they gross in the hundreds of millions each year.
The problem is that this is 1990's technology, and it almost always grows out of small implementations (along the lines of why some terribly complicated applications use MS Access).
Ultimately, it's important that people learn why to avoid this methodology, and how it directly improves what you do - even for small projects.
Best wishes