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?
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 ;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With