PostgreSql - Constraint Not Applied by a Trigger

Question

I have a table defined like this:

CREATE TABLE A (
   begin date,
   end date,
   CONSTRAINT ordered_dates CHECK ( begin <= end)
)

..and 2 triggers associated :

• trigger1 on BEFORE update
• trigger2 on AFTER update

In the trigger1, there's an insert in a second table B by computing the interval (=end-begin).

If the constraint ordered_dates is not respected , i have bad values inserted in table B. But the constraint seems to be check ONLY during the update NOT in the BEFORE trigger.

Do i have to test the ordered_date ONCE again in the trigger1 on before & evetually raise an exception in the trigger before ?? I know how to do that but i have code duplication in some way.

Answer 1

Your trigger is triggering before the insert, so it is getting the values before constraints have been applied.

Yes, you would need to check the values or apply the trigger after insert.

Answer 2

This is not a problem, because the transaction will be aborted when the CHECK constraint fires and fails, and thus the insert into table B will be (correctly) rolled back.