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.