Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL BEFORE UPDATE trigger - change value

So, I've got a MySQL table, named employees.

ID    name    meta
0     jack    ok
1     anne    del

I want to write a trigger which prevents a row where meta='del' to update the meta field. So, if I do:

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

The row should be updated and meta would be 'busy'

But when I do:

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

The meta field should still 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 returns with an syntax error. Any ideas?

like image 896
Flock Dawson Avatar asked Feb 08 '13 14:02

Flock Dawson


1 Answers

You forgot to add the SET clause. This way it doesn't actually change the value.

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 ;
like image 123
hadess20 Avatar answered Oct 09 '22 08:10

hadess20