Triggers can blossom into a performance problem. About the same time that happens they've also become a maintenance nightmare. You can't figure out what's happening and (bonus!) the application behaves erratically with "spurious" data problems. [Really, they're trigger issues.]
No end-user touches SQL directly. They use application programs. Application programs contain business logic in a much smarter and more maintainable way than triggers. Put the application logic in application programs. Put data in the database.
Unless you and your "users" don't share a common language, you can explain the constraint violations to them. The alternative -- not explaining -- turns a simple database into a problem because it conflates the data and the application code into an unmaintainable quagmire.
"How do I get absolute assurance that everyone's using the data model correctly?"
Two (and a half) techniques.
Make sure the model is right: it matches the real-world problem domain. No hacks or workaround or shortcuts that can only be sorted out through complex hand-waving explanations, stored procedures and triggers.
Help define the business model layer of the applications. The layer of application code that everyone shares and reuses.
a. Also, be sure that the model layer meets people's needs. If the model layer has the right methods and collections, there's less incentive to bypass it to get direct access to the underlying data. Generally, if the model is right, this isn't a profound concern.
Triggers are an train-wreck waiting to happen. Constraints aren't.