CODEWITHSUNDEEP
mysql
Johny
Johny

Reputation: 779

Mysql-Trigger syntax error

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

Upvotes: 0

Views: 42

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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 ;

Upvotes: 1

Related Questions