0
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.

FanoFN
  • 6,815
  • 2
  • 13
  • 33
laopan2008
  • 3
  • 2
  • I would think it would be the `comment` reference at the insert column list on `(table2_id, \`comment\`, record_created_date)` ... instead of with the `OLD` reference? – Paul T. Jan 21 '22 at 03:18
  • No, it's not. At least, HeidiSQL doesn't think there is a syntax error there. – laopan2008 Jan 21 '22 at 03:22
  • How did you try the OLD reference using the backticks? – Paul T. Jan 21 '22 at 03:24
  • If OLD could be replaced by something else, it might work. In my case, OLD.comment contains two reserved keywords. OLD is the problem here, I think. – laopan2008 Jan 21 '22 at 03:25
  • I tried all combinations -- put backticks around OLD, around comment, around both... – laopan2008 Jan 21 '22 at 03:26
  • What MySQL version? – FanoFN Jan 21 '22 at 03:26
  • MySQL version is 5.7 – laopan2008 Jan 21 '22 at 03:32
  • Both `COMMENT` and `OLD` are non-reserve keyword but `OLD` was only added [in mysql 8.0.14](https://dev.mysql.com/doc/refman/8.0/en/keywords.html). What is the full error message? – FanoFN Jan 21 '22 at 03:37
  • Well, when I checked MySql shell, it says: MySQL Shell 8.0.23. Anyway, 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. But, I am not using either single quote or double quote at all. – laopan2008 Jan 21 '22 at 03:43
  • I don't think that error caused by reserved keywords and it seems the query you posted does work in [this fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c0820c2e24ed3b842f8a33aa6bb51925) – FanoFN Jan 21 '22 at 03:50
  • How to tell that it works? Do you need to create table2 first? – laopan2008 Jan 21 '22 at 03:56
  • I think for creating the trigger, it doesn't matter if table2 have been created or not (as you can see in the fiddle). However, it doesn't make sense when the trigger is made to insert into a table that doesn't exists, isn't it? – FanoFN Jan 21 '22 at 04:00
  • On second thought, I'm beginning to think that the error you've received is not exactly from **creating** the trigger, right? – FanoFN Jan 21 '22 at 04:19
  • Unfortunately, it is. And, it has something to do with OLD. If a table name is a reserved word and has a column whose name is also a reserved word, for example, `interval`.`comment`, it works. But, in triggers, I have to use OLD and it fails. – laopan2008 Jan 21 '22 at 04:59

1 Answers1

1

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.

Akina
  • 39,301
  • 5
  • 14
  • 25