Create mysql trigger before insert (Syntax error)

Question

Hi i am creating some trigger for update the value in insert query before insert the row like this

    CREATE TRIGGER `Insert members in Posts` BEFORE INSERT ON
    `posts` FOR EACH ROW
    BEGIN
        DECLARE name_ varchar(100); 
        DECLARE contact_ varchar(16); 
        DECLARE status_ int; 
        DECLARE deleted_ int;
    SELECT
        `members`.`name`,
        `members`.`contact`,
        `members`.`status`,
        `members`.`deleted`
    INTO name_, contact_, status_, deleted_
FROM
    `members`
WHERE
    `members`.`member_id` = NEW.member_id;
SET NEW.member_name = name_;
SET NEW.member_contact = contact_;
SET NEW.member_status = status_;
SET NEW.member_deleted = deleted;
END

But i received the error like

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 4

I am little confused what's the error in line no 4. Please explain someone. Thanks.

Still i can't understand.. Just i need before insert the row i need to set new values from another table. That's all. Is this possible in triggers. — Sabish.M, Oct 09 '20 at 11:34
Does this answer your question? [Delimiters in MySQL](https://stackoverflow.com/questions/10259504/delimiters-in-mysql) — P.Salmon, Oct 09 '20 at 11:42

P.Salmon · Accepted Answer · 2020-10-09T11:47:24.113

Depending on the client/ide used you may need to set delimiter for example

 drop trigger if exists t;
   delimiter $$
   CREATE TRIGGER t BEFORE INSERT ON
    `posts` FOR EACH ROW
    BEGIN
        DECLARE name_ varchar(100); 
        DECLARE contact_ varchar(16); 
        DECLARE status_ int; 
        DECLARE deleted_ int;
    SELECT
        `members`.`name`,
        `members`.`contact`,
        `members`.`status`,
        `members`.`deleted`
    INTO name_, contact_, status_, deleted_
FROM
    `members`
WHERE
    `members`.`member_id` = NEW.member_id;
SET NEW.member_name = name_;
SET NEW.member_contact = contact_;
SET NEW.member_status = status_;
SET NEW.member_deleted = deleted;
END $$
delimiter ;

please review https://dev.mysql.com/doc/refman/8.0/en/stored-routines.html

Create mysql trigger before insert (Syntax error)

1 Answers1