I know it's a big ask and nobody else supports it, but it has been in the SQL standard since 1999....
Supporting select statements in check constraints e.g.
MY_DATE DATE CONSTRAINT NO_HOL CHECK (MY_DATE NOT IN(SELECT DATE FROM HOLIDAY_DATES))
CREATE ASSERTION NO_HOL CHECK NOT EXISTS (SELECT blah blah blah)
would mean we wouldn't have to use triggers to do anything other than trivial integrity constraints.