CODEWITHSUNDEEP
mysqlsequelpro
Leigh Scherrer
Leigh Scherrer

Reputation: 153

MySQL Table Trigger Correct Syntax

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

Upvotes: 0

Views: 526

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30839

You need to make two changes:

  1. table name should be enclosed in backticks, e.g. `my_backup_table`
  2. 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;

Upvotes: 1

Related Questions