MySQL Triggers to Disable A User Account

Question

I am trying to create a MySQL Trigger to disable someone's account if they have logged in to the site 3 times. I have tried to create this trigger using the following code, but it is not setting is_active to 0 no matter what times_logged_in is. Any help would be appreciated.

CREATE TRIGGER updateTrigger AFTER UPDATE ON users
FOR EACH ROW
BEGIN
UPDATE users SET is_active=0 WHERE NEW.is_code=1 
AND NEW.times_logged_in>=3
AND NEW.user_id=user_id;
END;

Answer 1

I'm not sure where user_id comes from in your trigger, looks like an extraneous check, try this:

CREATE TRIGGER updateTrigger AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  UPDATE users SET is_active=0 WHERE NEW.is_code=1 
  AND NEW.times_logged_in>=3
END;

Also, be sure that is_code = 1 is actually matching on the users you're updating, if it's not, it won't update any rows.

Answer 2

I would use a BEFORE UPDATE trigger instead:

CREATE TRIGGER updateTrigger BEFORE UPDATE ON users 
FOR EACH ROW 
BEGIN
IF NEW.is_code=1 AND NEW.times_logged_in>=3 THEN
   SET NEW.is_active=0;
END IF;
END;

Answer 3

You've hit a limitation of MySQL. Your table users invokes the trigger (AFTER UPDATE ON users), therefore the triggered code cannot modify it. See the MySQL-Manual:

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.