MYSQL Trigger not working

Question

I am trying to create the following trigger:

CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta
 FOR EACH ROW 
 BEGIN
 if new.wp_metakey = first_name then insert into test_customer(first_name) values (new.wp_meta_value);
 END

I keep getting a 1064 error that says, "You have an error in your SQL syntax; check the manual that corresopons to your MySQL server version for the right syntaxt to use near " at line 5"

What am I doing wrong?

Answer 1

The first problem is that you don't have an END IF;.

The next problem (assuming that you aren't doing this and just didn't include it in the question) is that when you have a compound statement, you have to change the statement delimiter from ; to something else so that the client will send the entire CREATE TRIGGER statement to the server as a single statement.

Finally, the column "first_name" probably needs to be qualified with NEW (assume you're referencing a column in wp_usermeta called "first_name".

DELIMITER $$
DROP TRIGGER IF EXISTS update_customers $$
CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta
FOR EACH ROW 
BEGIN
 IF NEW.wp_metakey = NEW.first_name THEN 
 INSERT INTO test_customer(first_name) values (NEW.wp_meta_value);
 END IF;
END $$
DELIMITER ;