Check constraint comparing values from multiple rows

Technically I think you could implement that in a constraint using a function. Downside however is that this is not properly supported by the Software Factory. It will lead to this function not being able to be modified without dropping the table / constraint that is being used.

What I’d do is implement this in a trigger. The update-trigger could look something like this:

if exists (select * 
             from inserted i
             join deleted d
               on d.vessel_pk = i.vessel_pk 
            where d.end_date is null         --check if there is a situation where there was no end-date
              and i.end_date is not null     --and this end-date has been updated to a non-empty value
              and not exists (select *       --where there are currently no non-end-dated vessels left of that perticular vessel_id 
                                from vessel v
                               where v.vessel_id = i.vessel_id 
                                 and v.end_date is null
                             )
          )
begin
    exec tsf_send_message at_least_one_vessel_should_have_no_end_date, null, 1 
end

Equally, you need to implement a trigger on the delete event in order to prevent a user deleting the last vessel record without an end-date.

Hope this makes sense?

(edit: sorry for the messed up layout of the SQL, paste it into Management Studio in order to have propper syntax highlighting)