MySQL BEFORE ADD trigger - change value

Question

So, I have a MySQL table called employees.

ID name meta 0 jack ok 1 anne del 

I want to write a trigger that prevents a line where meta = 'del' updates the meta field. So, if I do this:

 UPDATE employees SET meta = 'busy' WHERE ID = 0 

The line should be updated, and the meta will be "busy"

But when I do this:

 UPDATE employees SET meta = 'busy' WHERE ID = 1 

Meta field must be "del"

I tried:

 delimiter $$ CREATE TRIGGER updateEmployees BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF OLD.meta = 'del' THEN NEW.meta = 'del' END IF; END$$ delimiter ; 

But MySQL is returning with a syntax error. Any ideas?

Answer 1

You forgot to add the SET clause. Thus, this does not actually change the meaning.

 delimiter $$ CREATE TRIGGER updateEmployees BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF OLD.meta = 'del' THEN SET NEW.meta = 'del'; END IF; END$$ delimiter ; 

Answer 2

you missed ;

 delimiter $$ CREATE TRIGGER updateEmployees BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF OLD.meta = 'del' THEN NEW.meta = 'del'; -- << here END IF; END$$ delimiter ; 

Strike>

TRIGGER IS EVIL.

An alternative to a trigger is to add another AND condition

 UPDATE employees SET meta = 'busy' WHERE ID = 1 AND meta <> 'del'