CODEWITHSUNDEEP
mysqlsqltriggers
Pedro Alejandro
Pedro Alejandro

Reputation: 101

How to Create a Trigger MySQL 5.6

we are having a problem with the created_at on a WordPress system. Somehow some users get that field updated "randomly" to an invalid value, so it get's set as '00000000' datetime. We haven't found the cause on the WP code, and we are now analizing the few plugins the project has. It has a large Code and user base, so we thought it might be faster to use a MySQL trigger to catch this "random" updates.

The thing is we somehow keep on hitting a syntax error on or Trigger code, and lame SQLer's as we are, we need help trying to figure out what it could be.

What we are tying to accomplish is:

We decided to check for each row just in case there is some weird behaviour going on.

DELIMITER $$
CREATE TRIGGER `user_register_updated` BEFORE UPDATE ON `wp_t8y31tcd9u_users`
FOR EACH ROW
BEGIN
IF OLD.created_at != NEW.created_at

INSERT INTO user_registration_change_records (user_id, created_at) VALUES (OLD.ID, OLD.created_at) 
END IF;
END;$$
DELIMITER ;

Thanks in advance for any suggestions!

Syntax 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 'INSERT INTO user_registration_change_records (user_id, created_at) VALUES (OLD.I' at line 6

Upvotes: 0

Views: 1138

Answers (1)

Shadow
Shadow

Reputation: 34231

You have a couple of syntax errors:

  • missing then keyword before the insert (this is the one the error message relates to)
  • missing ; after the insert's row
  • extra ; after the end

At least these are the ones I can see.

DELIMITER $$
CREATE TRIGGER `user_register_updated` BEFORE UPDATE ON `wp_t8y31tcd9u_users`
FOR EACH ROW
BEGIN

    IF OLD.created_at != NEW.created_at THEN

        INSERT INTO user_registration_change_records (user_id, created_at) VALUES (OLD.ID, OLD.created_at); 
    END IF;   
END$$
DELIMITER ;

It is worthwile using mysql manual on compound statement syntax because all these can be found there.

Upvotes: 1

Related Questions