Most of the time when business logic is put into triggers it turn into a disaster. Even Oracle recommends only putting simple logic within triggers and for the most part should only be used for auditing purposes.

For example too many times I've updated a record with a new column value only to find out that the value I've updated because of some trigger caused the value to be set to null.

One advantage: you get all the write concurrency your RDBMS can give you for your transactions. Too many ORMs I've seen just have a Big Lock, leaving you with just one writer at a time.

I can't think of disadvantages to putting your business logic in the RDBMS. Can you elaborate?

> Too many ORMs I've seen just have a Big Lock, leaving you with just one writer at a time.

Geez, I think you've seen some pretty terrible ORMs. I don't know of any mature and popular ORMs that do that. If they do, I have no idea why they are popular or what makes anyone consider them mature.

ORMs that don't do that include: ActiveRecord, Sequel, Hibernate, Core Data, SQLAlchemy, etc.

I think he meant putting constraint and index logic in the code.

For example: I've seen people rely on a unique index in the code. But if you set a unique index in the DB you know it will be unique always. Even when the code is replaced some day.

I'm not sure I'd call constraints business logic, and I would definitely want to at least double-check them in the database.

I could be wrong, but I think the main reason business logic is put into the DB (and provided that the "users" of the DB are also segregated properly), is to present a cohesive view to all users of the DB.

Too often, when you have a "wide open" DB and multiple "users" of that DB (think groups within an org) decide to query it (whether just for reading, or worse, updating), the queries can often turn out to be radically different for the same business logic.

Maybe one development group thinks that a query should be done one way, while another thinks that to get the same data it should be done another, and now you have two or more groups disseminating data to other groups in totally different ways.

You may ask why the organization has multiple development groups, but it occurs. I worked for one company I won't name because it isn't important - as a web developer in their marketing department; our group was considered as separate from the IT group, which handled the main "DB" which was based around AS/400 systems and Apache SOLR - we worked together as best as possible, but also bumped heads, in that they wanted us to only work with their "DB" (I don't really consider SOLR to be a DB, though it can and is used like one by a variety of orgs) through their interface (which at times didn't work like they documented it - and many times changed without us knowing about it until our stuff broke mysteriously - usually around the end-of-year holiday push) - whereas we needed to store a lot of the stuff "nearline" in a MySQL DB (we used MySQL, PHP, and AWS for much of our development) to make things more responsive for our end users (ie - the people buying the products the company made off the websites we were creating). In essence, we a bit of a "split personality" going on - but our main boss was only two levels removed from the CEO of the company, so our stuff generally was tolerated - but it is a similar situation.

Where you have multiple dev groups (whether by design or because "ad-hoc" things occur - like Bob in accounting figuring out how to use ODBC with Excel macros to query the database for data for his and other departments), you can have this kind of chaos. When the DB is heavily controlled by IT, with the only "views" of it through tightly controlled business logic which is part of the DB, this kind of difference in data can be controlled.

But it does have many more downsides, which also means it probably shouldn't be used or done in that manner. Instead, the interface should probably be through a single interface (RESTful or similar), with the business logic in the code of that interface, and only the barest needed other logic in the DB to tie it together. Provided that the security on the DB is tightly controlled, and the only way other groups can access the data is via the exposed interface API, you can achieve the same results I think.

In a way, that's how we had the access at that one company - we had a RESTful interface API to the backend SOLR store; we could send a formatted "query object" and get back one or more "records" as a stream of data (we'd then usually take the data, parse through it and store parts of it into our MySQL DB - because the query/response time of that SOLR DB was horrible from a web development perspective; I don't think this was the fault of IT, but rather the fact that the datastore on the backend was vast, holding information about products dating back 60 or more years - I'm sure there was likely some COBOL in the mix somewhere).

That did have the downside of the fact that if we wanted a particular means to query for something that didn't exist in the existing API, we either had to make due with what we could do "locally" (thru code and/or mysql "buffering"), or we had to put in a request for a change to the IT group (which may or may not get accepted, and might take weeks for the turnaround time before we could use it). Furthermore, as I mentioned before, there were more than a few times that we rolled out a particular feature, only to have the website(s) that relied on that feature break because the backend API changed "behind our backs" (and we usually saw this over a holiday period, when our sales would peak of course). In many cases, we couldn't do anything about this (not even storing the SOLR information - it was too vast, plus there was a nagging idea that if we tried that someone's head would roll for not using the implemented interfaces and data that already existed - we could "buffer" or "cache" things, but we couldn't wholesale transfer the data over).