0

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 ;

1 Answer 1

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 ;
1
  • "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". Sep 26, 2019 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.