0

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?

ramya
  • 275
  • 1
  • 5
  • 13

0 Answers0