0

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

Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
Johny
  • 739
  • 1
  • 9
  • 16
  • 1
    possible duplicate of [error "1064" in trigger creation in mysql?](http://stackoverflow.com/questions/5003869/error-1064-in-trigger-creation-in-mysql) – Daniel W. May 27 '15 at 10:03
  • this has nothing to do with BEGIN, END.. triggers can have BEGIN, END – Johny Jun 08 '15 at 12:13

1 Answers1

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 ;
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • 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 – Johny May 27 '15 at 10:11
  • 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. – Abhik Chakraborty May 27 '15 at 10:14