check constraints vs. triggers while both of them written in PLPg/SQL

Question

I am using PostgreSQL 15.2; I can use both check constraints and triggers to ensure my business logic on database.

I read some of the comparisons about them, and I still have a question: if we can use PLPG/SQL for both of them and inside that custom function, we could user any select/insert/update/delete on any row on the same table or other tables, with accessing to NEW and OLD values of a row in both of them (trigger and check), what is the real difference between them? is choosing between them depend on the function body ?

I tried both ways in some cases. I don't know the performance effects.

Answer 1

A check constraint and a trigger are two different things.

1. A check constraint should only access other columns in the table and only through an expression, not a function. You can create a check constraint with a function but Postgres does not track that dependency. So when you do an upgrade , say via dump/restore you will get errors as the table creation command will be looking for a function that does not exist yet. Also a check constraint does not have access to NEW/OLD. Furthermore it cannot change values for other fields in the table.

2. A trigger has to run its code from a function. That code can touch pretty much anything in the database. The function has access to NEW and/or OLD depending on the event(INSERT/UPDATE/DELETE) or timing(BEFORE/AFTER).