MySQL Table Trigger Correct Syntax

Question

I'm trying to add a table trigger to MySQL DB using Sequel Pro. The behavior I want is this:

Whenever a specific column in one of my tables is updated, I want to record the old value in another table so I can reference it later.

I'm using the GUI to create table triggers:

Action Time: Before Event: Update

IF NEW.status = "Some String" THEN
INSERT INTO 'my_backup_table' (id, status) VALUES (OLD.id, OLD.status)
END;

I keep getting syntax errors: The specified trigger was unable to be created.

MySQL said: 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 ''my_backup_table'

Any help is really appreciated! Thank you

Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — Paul Spiegel, Apr 27 '17 at 18:48

score 1 · Answer 1 · answered Apr 27 '17 at 18:51

You need to make two changes:

table name should be enclosed in backticks, e.g. `my_backup_table`
IF should end with END IF

Below should work:

IF NEW.status = "Some String" THEN
INSERT INTO \`my_backup_table\` (id, status) VALUES (OLD.id, OLD.status)
END IF;

MySQL Table Trigger Correct Syntax

1 Answers1