Then Don’t Do That (tm)
It’s hardly ever a good idea to have multiple apps writing to the same set of tables directly.
If you use triggers for validation, you can rely on your database's MVCC to resolve any conflicts between triggers. Note that this is significantly more performant and robust than the application trying to resolve, or even notice, those conflicts.
But as soon as you add another service with write capability, you need to re-implement the validation logic. What if you forget?
In context of application validation logic, the logic is only living in one place.
Triggers are basically “spooky action at a distance”.
It’s also much easier to promote, version, upgrade, and rollback application changes.
Suppose you are running a booking system for ridesharing. When you want to lock-in a passenger's seat, it's possible that consistency can be violated when you do it at the application level: both A and B request a seat after a query returns that there is a seat available, then simultaneously lock-in their reservation after seeing stale data.
It's also naive to think that validation in the application is sufficient. For, say, a webapp, there should be three places where that occurs: at the client where you can provide the most meaningful feedback, at the service to catch most consistency errors and guard against malicious actors while you can provide meaningful feedback, and at the database level for the most perennial and slow-moving constraints so that concurrency and your own bugs don't fuck up your source-of-truth.
edit: come to think if it, it's possible to do it without triggers as long as you can still express the constraint as a query: append a boolean field to each table with a TRUE check constraint, and use a table query mirroring the constraint to populate that field on every mutation.
This is a solved problem.
function reserveSeat (customerid, seatid)
{
lock
{
updatedRows = update seats set customerid = {customerid} where id= {seatid} and customerid is null
return updatedRows == 1
}
}
Otherwise it's a fantastic idea.
But what happens when you need to change something about the database. Isn’t it a lot easier to have all of the code in one place?
So the usual retort is to put all of the business rules in stores procedures.
Then you have an unholy mess of a database with triggers and stored procedures that are harder to modify, harder to unit test, harder to version and just an unmaintainable mess.
I’ve never heard a single developer say that they love maintaining a system with 100s of large stores procedures, triggers, etc.