Error creating MySQL Trigger

Question

I'm trying to create this trigger but i'm getting

[Err] 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 '; CREATE TRIGGER ca_passwd_trigger BEFORE UPDATE ON users FOR EACH ROW BEGIN ' at line 1

delimiter $$
DROP TRIGGER IF EXISTS ca_passwd_trigger ;
$$
CREATE TRIGGER ca_passwd_trigger BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    IF ((NEW.passwd <=> OLD.passwd) = 0) THEN
        SET NEW.passwd_modified_at = NOW();
    END IF;
END;$$
delimiter ;

Arulkumar · Accepted Answer · 2016-05-31 20:22:24Z

3

In your query you added the query terminator ; with the delimiter $$ in two places. The below query is having proper query terminators and delimiters.

DELIMITER $$
DROP TRIGGER IF EXISTS ca_passwd_trigger; -- removed the delimiter $$ after the terminator ;
CREATE TRIGGER ca_passwd_trigger BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    IF ((NEW.passwd <=> OLD.passwd) = 0) THEN
        SET NEW.passwd_modified_at = NOW();
    END IF;
END$$  -- removed the terminator ; before the delimiter $$
DELIMITER ;

edited May 31, 2016 at 20:22

answered May 31, 2016 at 19:56

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error creating MySQL Trigger

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related