I am trying to execute the following trigger. But,resulting in MySQL error.
Mysql2::Error: 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 'IF (max_dis_id IS NULL) THEN
CREATE TRIGGER add_event_display_id BEFORE INSERT ON events
FOR EACH ROW
BEGIN
DECLARE event_dis_id integer;
DECLARE max_dis_id integer;
IF (new.display_id IS NULL) THEN
SELECT max(display_id) INTO max_dis_id FROM events WHERE event_type = new.event_type AND location_id = new.location_id;
SET event_dis_id = 0
IF (max_dis_id IS NULL) THEN
SET max_dis_id = 1;
ELSE
SET max_dis_id = max_dis_id + 1;
END IF;
if event_dis_id > max_dis_id then
SET new.display_id = event_dis_id;
ELSE
SET new.display_id = max_dis_id;
END IF;
END IF;
END;
Seems like the syntax is correct. Can someone please help?