mercredi 15 novembre 2017

Mysql Trigger with IF and IF ELSE conditions

i have table 'comments' with fields 'type','receiver_id' ,'confirm' and 'id'. there are three types of object that each have comment section. after comment confirm the trigger increase the number of objects in their respective table.

DB::unprepared('CREATE TRIGGER comment_confirm
        AFTER UPDATE ON comments FOR EACH ROW
        BEGIN
            IF OLD.confirm = 0 AND NEW.confirm = 1 THEN
                IF OLD.type = profile THEN
                    UPDATE profiles SET comments = comments + 1 WHERE user_id = OLD.reciever_id;
                ELSE IF OLD.type = blog THEN
                    UPDATE blogs SET comments = comments + 1 WHERE user_id = OLD.reciever_id;
                ELSE IF OLD.type = topic THEN
                    UPDATE topics SET comments = comments + 1 WHERE user_id = OLD.reciever_id;
                END IF;
            END IF;
        END
        ');

the error for migration is:

[PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 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 13

Aucun commentaire:

Enregistrer un commentaire