Mysql-Trigger syntax error

Question

I'm trying to create a trigger on a table, but I keep getting an error. Any idea what's wrong with the following statement?

CREATE TRIGGER `some_name` BEFORE UPDATE ON `some_table` 
    FOR EACH ROW BEGIN 
        IF NEW.isDeleted = 1 THEN
            SET NEW.isSearchable = 0; 
        ELSE THEN 
            SET NEW.isSearchable = 1; 
        END IF;
    END;

Mysql output:

#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 '' at line 4

possible duplicate of error "1064" in trigger creation in mysql? — Daniel W.
– Daniel W., Commented May 27, 2015 at 10:03
this has nothing to do with BEGIN, END.. triggers can have BEGIN, END — Johny
– Johny, Commented Jun 8, 2015 at 12:13

Abhik Chakraborty · Accepted Answer · 2015-05-27 10:01:26Z

1

You are missing delimiter and also no need of then after else

delimiter //
create trigger `some_name` BEFORE UPDATE ON `some_table` 
for each row
begin
 if new.isDeleted = 1 then
  SET NEW.isSearchable = 0; 
 else
  SET NEW.isSearchable = 1; 
 end if;
end;//

delimiter ;

answered May 27, 2015 at 10:01

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

2 Comments

Johny Over a year ago

Hi thanks. I tried delimiter and I get an error back! but somehow the trigger gets created although MySQL returns an error! strange! That's why I thought it was not working ------- #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 'DELIMITER' at line 1

Abhik Chakraborty Over a year ago

are you running this in mysql cli or some other 3rd part app like PHP myadmin, if its on php myadmin check there you can set a delimiter and remove the delimiter at the beginning and end from the above code.

Collectives™ on Stack Overflow

Mysql-Trigger syntax error

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related