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.

Welcome to SO - you might want to read about the supported markdown for future questions: stackoverflow.com/editing-help — OMG Ponies
– OMG Ponies, Commented Jan 5, 2010 at 23:24

Bear · Accepted Answer · 2010-01-05 23:30:47Z

2

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.

answered Jan 5, 2010 at 23:30

Bear

1,1377 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alvherre · Accepted Answer · 2010-01-06 14:34:43Z

0

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.

answered Jan 6, 2010 at 14:34

alvherre

2,60921 silver badges22 bronze badges

Collectives™ on Stack Overflow

PostgreSql - Constraint Not Applied by a Trigger

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related