mysqltriggerskeywordreserved

In MySql triggers, what is the correct syntax for referring to a column name which is a reserved word?

CREATE TRIGGER `db_name`.`trigger_name` BEFORE DELETE ON `db_name`.`table1_name` 
FOR EACH ROW 
BEGIN 
INSERT INTO `db_name`.`table2_name`(table2_id,`comment`,record_created_date) 
VALUES (OLD.id, OLD.`comment`, NOW()); 
END

The syntax error happens at "OLD.comment" because comment is a reserved word in MySql and it's after OLD, the query above doesn't work even with backtick ("`").

Of course, the easiest solution is to change the column name. But, it's not an option in my case. Please help if you know the correct syntax. Thanks.

Solution

  • the error message is: SQL Error (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 5.

    Line 5 is the line which contains first semicolon in your code. You forget about DELIMITER reassign.

    So

    DELIMITER ;;

CREATE TRIGGER `db_name`.`trigger_name` BEFORE DELETE ON `db_name`.`table1_name` 
FOR EACH ROW 
BEGIN 
INSERT INTO `db_name`.`table2_name`(table2_id,`comment`,record_created_date) 
VALUES (OLD.id, OLD.`comment`, NOW()); 
END
;;

DELIMITER ;

    But your trigger consists from only one statement, so remove both BEGIN and END lines. DELIMITER reassign not needed in this case.

    CREATE TRIGGER `db_name`.`trigger_name` BEFORE DELETE ON `db_name`.`table1_name` 
FOR EACH ROW 
INSERT INTO `db_name`.`table2_name`(table2_id,`comment`,record_created_date) 
VALUES (OLD.id, OLD.`comment`, NOW());

    PS. Use autoutilizing (DEFAULT CURRENT_TIMESTAMP) for db_name.table2_name.record_created_date column, and you may skip it away from INSERT.