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 ;

• Jan Steinman: "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." NICE! I've been wanting to, upon INSERT, create a record in another table, and then reference that new record in the record that caused the INSERT trigger. Now I know how to do it — with a separate "join table".