MySQL触发器禁用用户帐户 [英] MySQL Triggers to Disable A User Account

问题描述

我正在尝试创建一个MySQL触发器来禁用某人的帐户（如果该用户已登录3次）。我尝试使用以下代码创建此触发器，但无论times_logged_in是什么，都不会将is_active设置为0。任何帮助将不胜感激。

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;

推荐答案

您遇到了MySQL的限制。您的表 users 会调用触发器（对用户的更新），因此触发的代码无法对其进行修改。请参见 MySQL手册：

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.

这篇关于MySQL触发器禁用用户帐户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！