MySQL trigger/check on INSERT based on multiple factors (location, date and time)

Question

I have a shift schedule planner for various warehouses. I'm trying to add multiple checks at DB level. If I'm using a composite key (by removing shift_id) to match staff, location,date time it's works but it doesn't solve the shift overlapping problem (e.g if I insert exact same data as shift_id 1 but instead of 09:00 start time is 09:01 will accept as valid shift).

In below example, the INSERT should fail for shift_id 2 because, the shift time is overlapped for same staff_id 1 on same date The next available shift for same staff (on same date and same location or a different one) should be available after 13:00. For shift_id 3 should be another fail because staff_id 1 is already scheduled for exact same date and same hours to another location (location_id 1).

I was trying to add table constraint to compare shift_start and shift_end but unsuccessfully:

 ADD CONSTRAINT NoDoubleShifts CHECK(
    NOT EXISTS (
            SELECT 1
            FROM Shift S1, Shift S2
            WHERE s1.shift_date + s1.shift_start < s2.shift_date + s2shift_start
            AND s1.shift_date + s1.shift_end > s2.shift_Date + s2shift_start)

MySQl throw errors (I'm guessing that the constraint is not well formatted "A statement was found, but no delimiter betwen it near CHECK").

The second trying solution was to add a trigger BEFORE INSERT in different combinations but didn't work either. Is calculating the time but is preventing me to add any other staff (with different id) during that time slot.

begin
  if exists (select * from shift
             where shift_start <= new.shift_end
             and shift_end >= new.shift_start ) then
    signal sqlstate '45000' SET MESSAGE_TEXT = 'Shifts are overlapping';
  end if;
end;

Any help and constructive comments or anyone to pint me in right direction will be highly appreciated.

Thanks in advance..

Answer 1

With some help, the solution was extremely simple... The trigger needs to validate the staff id as well. Therefore, the final trigger would look like:

begin
  if exists (select * from shift
             where shift_start <= new.shift_end
             and shift_end >= new.shift_start
             and staff_id = new.staff_id) then
    signal sqlstate '45000' SET MESSAGE_TEXT = 'Shifts are overlapping';
  end if;
end

Answer 2

Instead of writing a TRIGGER, write a STORED PROCEDURE to do both the INSERT and all the tests you need around it. Then CALL the SP. This will give you more flexibility.