CHECK constraint and trigger

Hi,

I have one table where I want one column ("canceled_date") to be updated by a trigger function whenever the boolean "canceled" column is set to true. The problem is that I also want to have a CHECK constraint defined on that table that makes sure no overlapping bookings exist, and the trigger works fine until I add the chk_not_overlaps contraint (see below).

=> insert into booking(customer, resource, start_time, end_time)
values (1,1,'2006-12-01','2006-12-02');

Query returned successfully: 1 rows affected, 20 ms execution time.

=> update booking set canceled=true where id=1;

ERROR: new row for relation "booking" violates check constraint "chk_not_overlaps"
SQL state: 23514

=> alter table booking drop constraint chk_not_overlaps;

Query returned successfully: 1 rows affected, 20 ms execution time.

=> update booking set canceled=true where id=1;

Query returned successfully: 1 rows affected, 20 ms execution time.

I have tried altering the trigger event (before/after insert or update) without success. Any ideas?

Pg version is 8.2b for Windows (PostgreSQL 8.2beta2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special))

----------------------------------------------------------------------------------------
create table booking
(
id bigserial,
customer integer not null,
resource integer not null,
added_date timestamp not null default now(),
canceled boolean not null default false,
canceled_date timestamp,
start_time timestamp not null,
end_time timestamp not null,
payment_recieved boolean not null default false,
pickup_time timestamp,
return_time timestamp,
constraint pk_booking primary key (id)
);

create or replace function not_overlaps(bigint, timestamp, timestamp) returns boolean as
'select
case when sum(id) > 0 then
false
else
true
end
from booking
where resource = $1
and (start_time, end_time) overlaps ($2, $3)
and canceled = false
group by resource'
language sql;

alter table booking
add constraint chk_not_overlaps check (not_overlaps(resource, start_time, end_time));

create or replace function tf_set_canceled_date() returns trigger as
$$
declare
begin
if NEW.canceled=true then
NEW.canceled_date = now();
end if;
return NEW;
end;
$$
language plpgsql;

create trigger tgr_booking_modified
before insert or update on booking
for each row execute procedure tf_set_canceled_date();

----------------------------------------------------------------------------------------

Koppla av och tjäna pengar på din skicklighet på Spray Spel! http://www.spray.se/underhallning/spel/