CODEWITHSUNDEEP
mysqlsql
Marta Monteiro
Marta Monteiro

Reputation: 17

Compare datetimes before insertion of new record in a table

I have a table (appointment) that stores info about their workers meetings composed by their employee ID and a datetime with the meeting schedule. I want to check before insertion of a new record in the table if there's no overlap of meetings. The average time for a meeting is 30 minutes, so I need to compare the new date with all the others in the tables for that employee.

CREATE TABLE appointment (
datentime DATETIME NOT NULL,
employeeID int,
constraint PK_appointment PRIMARY KEY (datenhora, employeeID),
);

I created this trigger but I'm new and I'm having problems with this.

drop trigger if exists chk_datentime_C_U;
DELIMITER $$
CREATE TRIGGER chk_datentime_C_U BEFORE INSERT ON appointment FOR EACH ROW
BEGIN
select * from appointment where employeeID=new.employeeID;
    select DATEDIFF(minute, new.datentime, datentime) AS DateDiff;
    if (-30<=DateDiff<=30) then 
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'check date and time';   
    end if;
END
$$
DELIMITER ;

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

The logic to get the overlapping appointments is:

select a.*
from appointment a
where a.employeeID = new.employeeID and
      a.datentime > new.datentime - interval 30 minute and
      a.datentime < new.datentime + interval 30 minute ;

If there are no overlaps, then this returns no rows.

I'll let you put this logic into the trigger.

Upvotes: 1

Related Questions