Update Trigger in MYSQL

Question

Here is my requirement :-

I have a table called "users" and field called "Admin". Field "id" is primary key. Wants to update "Admin" field to "1" if id=1000;

Tried this ,but not working.I tried "New" instead of "old".Also I tried "After" instead of "Before". But it is not working..

Any suggestions please...

DELIMITER $$
CREATE TRIGGER update_user_admin
Before UPDATE ON users
FOR EACH ROW
BEGIN

  UPDATE users
    SET admin = 0
  WHERE old.id = 672;

END;
$$
DELIMITER ;

Answer 1

Unfortunately you can't use a trigger to update the table that called the trigger: from the documentation:

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Could you move the admin field to a separate table which just holds the current admin id (or list of), and then you could update that with the trigger.

(of course if your admin is always id:1000, then you probably don't need the trigger to start with)

You would also need to consider whether to fire this AFTER INSERT and/or UPDATE.

An example trigger would be:

DELIMITER $$
CREATE TRIGGER update_user_admin
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF new.id = 1000 THEN
        UPDATE userAdmin SET id = new.id;
    END IF;
END;
$$
DELIMITER ;