CODEWITHSUNDEEP
mysqlsqltriggers
Flock Dawson
Flock Dawson

Reputation: 1878

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?

Upvotes: 16

Views: 39087

Answers (3)

user20687527
user20687527

Reputation: 1

simple logic, check first those column value declare unique in db (may use concat and where clause pk id). if not exist then continue update and set value include the unique constraint column stated, else (if exist) update only column that not declare in unique constraint. logic may check on page or can run on stored procedure. leave the trigger alone.

Upvotes: 0

hadess20
hadess20

Reputation: 281

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 ;

Upvotes: 28

John Woo
John Woo

Reputation: 263743

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 ;

TRIGGER IS EVIL.

An alternative to trigger is by adding another condition AND

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

Upvotes: 0

Related Questions