Check Constraint Across Parent-child Tables

Hi,

DDLs and DMLs:
create table #job (jobID int identity(1,1) primary key, jobName
varchar(25) unique not null, jobEndDate dateTime, jobComplete bit
default(0), check (([JobEndDate] is null and [JobComplete] = 0) OR
([JobEndDate] is not null and [JobComplete] = 1)));

Q1 with check constraint:
sample dagta
insert into #job (jobName)
values('first job');

transaction A
update #job
set jobEndDate = '12/19/2003', JOBCOMPLETE=1
where jobID = 3;
RESULTSET/STATUS = Success

update #job
set jobEndDate = NULL, JOBCOMPLETE=0
where jobID = 3;
RESULTSET/STATUS = Success

transaction C
update #job
set jobEndDate = '12/19/2003'
where jobID = 3;
RESULTSET/STATUS = Failure

how come check constraint can't set a value which is preset in the
check constraint? If it's the way how it works with MS SQL Server
2000, well, IMHO, it's limiting because the above transaction C is a
valid one. Or maybe check constraint is not fit for this purpose?

Maybe, it doesn't make much sense for me to go into Q2 but I'll try
-- create job's child table, task
create table #task (taskID int identity(1,1) primary key, taskName
varchar(25) unique not null, taskEndDate dateTime, taskComplete bit
default(0), jobID int not null references #job (jobID));
-- skip check constraint for taskEndDate and taskComplete for now

Now, the Business Rule says,
1) if all tasks are complete then automatically set jobComplete for
the #job table to yes;
2) the jobEndDate in the #job table must be >= the last/Max
taskEndDate

I tend to think trigger would slow down data update quite a bit, so,
try to stay away for this purpose if possible.

Always appreciate your idea.