I'm with a problem during creationg of a trigger in my database. I've a MySQL Second Generation instance with a database (name: test) in my google cloud sql.
Right now i have multiple tables in my database and im trying to create a trigger in one of that tables using:
CREATE TRIGGER date_overlap_insert_start_date
BEFORE INSERT ON driver_operation
FOR EACH ROW
BEGIN
if exists(
select 1
from driver_operation
where nif = NEW.nif
and (NEW.start_date > start_Date and NEW.start_date < end_Date)) then
signal sqlstate '45000' SET MESSAGE_TEXT = 'Overlaps with existing data';
end if;
END;
The error i get is this: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 10
Is there anyone who can help me on this? This trigger is to prevent overlap in date fields.
I had this exact same issue today. It is simply due the cloud sql instance viewing each semi colon as the end of a statement, so I can only assume it tries to execute each time it encounters one.
Try creating your trigger with:
/*!50003 CREATE*/ /*!50003 TRIGGER date_overlap_insert_start_date
BEFORE INSERT ON driver_operation
FOR EACH ROW
BEGIN
if exists(
select 1
from driver_operation
where nif = NEW.nif
and (NEW.start_date > start_Date and NEW.start_date < end_Date))
then
signal sqlstate '45000' SET MESSAGE_TEXT = 'Overlaps with
existing data';
end if;
END */
edited to fix my syntax. Was missing a semi-colon.