mysql中使用触发器进行数据验证与完整性检查

触发器中不能用SELECT ... INTO查询当前表以检查主键冲突，应改用EXISTS配合SIGNAL；BEFORE UPDATE中需直接赋值NEW字段才生效；触发器无法替代外键且不参与事务回滚；DELETE触发器中不可用NEW，INSERT中不可用OLD。

触发器里不能用 SELECT ... INTO 变量来查主键冲突

MySQL 触发器中想验证某条记录是否已存在，常见错误是写 SELECT id INTO @exists FROM users WHERE email = NEW.email;，再判断 @exists。这在 BEFORE INSERT 中会失败——因为 SELECT ... INTO 在触发器里不支持对当前表的查询（会报 ERROR 1442: Can't update table 'users' in stored function/trigger because it is already used by statement which invoked this stored function/trigger）。

正确做法是用 EXISTS 子句配合 IF 判断，或更干脆地改用唯一约束 + 自定义错误信息：

DELIMITER $$
CREATE TRIGGER check_email_unique_before_insert
    BEFORE INSERT ON users
    FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM users WHERE email = NEW.email) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email already exists';
    END IF;
END$$
DELIMITER ;

BEFORE UPDATE 触发器中修改 NEW 字段才能生效

想在更新前强制修正数据（比如把空字符串转为 NULL、统一大小写），必须直接赋值给 NEW.column_name。只声明局部变量或执行 SET @var = ... 不会影响最终写入的值。

常见场景包括：

• 防止 phone 字段存空字符串：IF NEW.phone = '' THEN SET NEW.phone = NULL; END IF;
• 自动标准化邮箱格式：SET NEW.email = LOWER(TRIM(NEW.email));
• 限制字段长度（避免截断静默发生）：IF LENGTH(NEW.description) > 500 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Description too long'; END IF;

触发器无法替代外键，且不参与事务回滚的“假安全感”

很多人以为在子表插入前用触发器查父表是否存在某 category_id 就能模拟外键，但这是危险的幻觉。触发器里的 SELECT 不加锁，高并发下仍可能产生脏读；而且 MySQL 的触发器本身不支持在触发器里执行 ROLLBACK，只能靠 SIGNAL 抛异常中断语句——这会让整个外部事务回滚，但逻辑上你只想拒绝这一行，而非连带干掉前面成功的几行插入。

启科网络PHP商城系统

启科网络商城系统由启科网络技术开发团队完全自主开发，使用国内最流行高效的PHP程序语言，并用小巧的MySql作为数据库服务器，并且使用Smarty引擎来分离网站程序与前端设计代码，让建立的网站可以自由制作个性化的页面。 系统使用标签作为数据调用格式，网站前台开发人员只要简单学习系统标签功能和使用方法，将标签设置在制作的HTML模板中进行对网站数据、内容、信息等的调用，即可建设出美观、个性的网站。

下载

真正该做的是：

• 优先用原生 FOREIGN KEY（确保引擎是 InnoDB，且父子表字符集、字段类型完全一致）
• 若因分库/历史原因不能用外键，验证逻辑应放在应用层或存储过程中，而不是依赖触发器
• 触发器只做轻量级、确定性转换（如时间戳归一化、状态码映射），不做跨表强一致性检查

INSERT 和 UPDATE 触发器共享 NEW，DELETE 共享 OLD，别混用

写 BEFORE DELETE 时误用 NEW.id 是高频低级错误——NEW 在 DELETE 触发器中根本不可用，只会报 Unknown column 'NEW.id' in 'field list'。同理，OLD 在 INSERT 触发器中也不存在。

记住口诀：INSERT/UPDATE 看 NEW，DELETE 看 OLD，UPDATE 同时有两者（OLD.status 对比 NEW.status 做状态流转校验很常用）。

例如禁止降级用户角色：

DELIMITER $$
CREATE TRIGGER prevent_role_downgrade
    BEFORE UPDATE ON users
    FOR EACH ROW
BEGIN
    IF OLD.role = 'admin' AND NEW.role != 'admin' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Admin role cannot be changed';
    END IF;
END$$
DELIMITER ;