MySQL trigger if condition exists(如果条件存在,则 MySQL 触发器)
问题描述
我正在尝试编写一个更新触发器,它只会在更新语句中设置新密码时更新密码,但我在尝试确定语法时遇到了很糟糕的时间.这应该很简单,但我只是没有找到解决方案.
I'm trying to write an update trigger that will only update a password when a new password is set in the update statement but I'm having a terrible time trying to nail down the syntax. This should be a no-brainer but I'm just not finding the solution.
这是我的代码:
CREATE TRIGGER upd_user BEFORE UPDATE ON `user`
FOR EACH ROW BEGIN
IF (NEW.password <> '') THEN
SET NEW.password = PASSWORD(NEW.password);
END IF;
END;
我试过了:
IF (NEW.password <> NULL) THEN
IF (NEW.password) THEN
IF NEW.password <> NULL THEN
IF (NEW.password > 0) THEN
IF (NEW.password != NULL) THEN
而且我确定还有许多其他组合,但它只是不起作用.有没有人有任何见解?
And I'm sure many other combinations but it's just not working. Does anyone have any insight?
推荐答案
我认为您的意思是在未提供新密码时将其更新回 OLD
密码.
I think you mean to update it back to the OLD
password, when the NEW one is not supplied.
DROP TRIGGER IF EXISTS upd_user;
DELIMITER $$
CREATE TRIGGER upd_user BEFORE UPDATE ON `user`
FOR EACH ROW BEGIN
IF (NEW.password IS NULL OR NEW.password = '') THEN
SET NEW.password = OLD.password;
ELSE
SET NEW.password = Password(NEW.Password);
END IF;
END$$
DELIMITER ;
然而,这意味着用户永远无法清除密码.
However, this means a user can never blank out a password.
<小时>如果密码字段(已经加密)在更新到 mySQL 时被发送回,那么它不会为空或空白,并且 MySQL 将尝试在其上重做 Password() 函数.要检测这一点,请改用此代码
If the password field (already encrypted) is being sent back in the update to mySQL, then it will not be null or blank, and MySQL will attempt to redo the Password() function on it. To detect this, use this code instead
DELIMITER $$
CREATE TRIGGER upd_user BEFORE UPDATE ON `user`
FOR EACH ROW BEGIN
IF (NEW.password IS NULL OR NEW.password = '' OR NEW.password = OLD.password) THEN
SET NEW.password = OLD.password;
ELSE
SET NEW.password = Password(NEW.Password);
END IF;
END$$
DELIMITER ;
这篇关于如果条件存在,则 MySQL 触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:如果条件存在,则 MySQL 触发器
基础教程推荐
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01