When does a check constraint execute?

I've been working with a time travel table as discussed in "Developing
Time-Oriented Database Applications in SQL" by Richard Snodgrass.

I attempted to build a constraint for a table that disallowed
overlapping time itervals as follows:

test=# create table test_dates (from_date date, to_date date);
CREATE TABLE
test=# create or replace function no_overlaps()
test-# returns boolean
test-# language sql
test-# as
test-# $$ select not exists
test$#     (select * from test_dates t1
test$#       where 1 < ( select count(*) from test_dates t2
test$#                   where (t1.from_date, t1.to_date) overlaps
test$#                            (t2.from_date, t2.to_date)) )$$;
CREATE FUNCTION

Now lets test our function:

test=# insert into test_dates values (date 'Jan 1, 2006', date 'Jan 31, 2006');
INSERT 0 1
test=# insert into test_dates values (date 'Feb 1, 2006', date 'Mar 1, 2006');
INSERT 0 1
test=#

This should return true

test=# select no_overlaps();
 no_overlaps
-------------
 t
(1 row)

Good the two intervals do not overlap
lets add an overlapping interval

test=# insert into test_dates values (date 'Jan 15, 2006', date 'Feb 15, 2006');
INSERT 0 1

test=# select no_overlaps();
 no_overlaps
-------------
 f
(1 row)

Good. Now lets remove the overlapping interval and try that and add a check
constraint to test_dates

test=# delete from test_dates where from_date = date 'Jan 15, 2006';
DELETE 1
test=# alter table test_dates add constraint check_overlaps check(no_overlaps());
ALTER TABLE
test=# insert into test_dates values (date 'Jan 15, 2006', date 'Feb 15, 2006');
INSERT 0 1

what? The check constraint should have prevented that insert, shouldn't it?

Let's make sure we haven't messed up.

test=# select no_overlaps();
 no_overlaps
-------------
 f
(1 row)

Nope, It looks like the check() constraint is being fired before the
insert occurs.  I'm wondering if that's correct?  I have tried this on
SQL Server 2000/2005 and both of those seem to fire the check constraint
after the insert.

I do not know whether this is a bug or not?  I suspect that what's
really happening is that I'm abusing CHECK constraints, and that this
should more properly be handled by a trigger.

Edmund