The overriding reason is that a trigger (compared to a constraint) does not represent a static invariant which one can then reason about, and potentially manipulate further - or serve as the basis for query optimisations (for example).
When your RDBMS says that a particular CHECK constraint is enabled, then that’s a hard-and-fast guarantee that every row in that table satisfies that constraint. As a concrete example: if I have a simple CHECK constraint that ensures the Users.EmailAddress column matches a regex like “^\S+?@[\w\.\_\-]$”, then my ETL code that reads those records out from the DB doesn’t need to handle the case when “@“ is missing, thus greatly simplifying software design.
Put another way: consider how refinement-types (or dependent-types) in OOP exist to declare and enforce hard guarantees about the state or shape of an object or data in-memory, and they also provide effectively zero-cost compile-time precondition checks; then you can appreciate how constraints in a databases do the same, except for persisted data on-disk.
But a trigger, on the other-hand, needs some qualification as there are many kinds - we can ignore triggers which simply audit (I.e. log or alert) when some action is taken, but there are DML triggers which perform some test on data affected by the DML and either block the DML from continuing or committing, or allow it to happen. This kind of trigger is functionally identical to a CHECK constraint - but unlike a CHECK it doesn’t provide any guarantees. While he third kind of trigger won’t block DML, but it instead applies some transformation to the data affected by the DML before the change is committed; this has the advantage of allowing otherwise-invalid data to be corrected and accepted instead of blocking the operation; all of this is good stuff, honestly.
…the problem is that the presence and enabled/disabled statue of the trigger tells you absolutely nothing about the state of the data at-rest - but a CHECK, FK, or UNIQUE CONSTRAINT does.
Consider that if I removed that e-mail address regex CHECK and moved it into a block/allow TRUGGER then anyone who reads from my Users table now no-longer has any guarantees about the contents of the Email column; Now also let’s suppose that some other DBA, or perhaps an automated process, might need to legitimately disable the TRIGGER temporarily while they perform data cleanup work and then re-enable it; in the event that the cleanup job was botched then the invalid email addresses will lie there undiscovered until the ETL loader crashes the next time it runs because it was never written to handle malformed email addresses.
So in truth, TRIGGERs and CONSTRAINTS are complementary to each other: a DML trigger that transforms data to satisfy a CONSTRAINT is a good example.
————
Now consider an abort/allow trigger that performs something “non-trivial” (as far as the DBMS is concerned) DML validation check, such as a cross-table, or even cross-database query; obviously that won’t scale and it also introduces more points of failure (ext. DB connections) - but hang on: for limited kinds of cross-table validation checks we use FK CONSTRAINTS as that’s what they’re designed for - and they work great because we know how to optimise for them (usually by adding FK indexes).
So my argument is that there exist many other classes of validation rules which today can only be implemented as TRIGGERs but could instead be better implemented as a declared CONSTRAINT because of the hard guarantees that only declarative constraints can give (when enabled, of course).
For example, a “non-unique FK” constraint would not be any more expensive to enforce than an FK because it reduces down to the same (indexed!) set-membership test as a normal FK constraint. The exact same applies to an “anti-foreign-key” constraint (except the set-membership test is inverted). It’s so trivial to implement - which means its so maddening that it doesn’t exist in SQL.
My position is thus: if you can express a desired constraint as a deterministic function of a single row and the set of all indexes which runs within at-most O( n log n ) time proportional to the initiating DML’s time complexity then we should be allowed to do it ourselves.