MySQL实现参照完整性触发器遇referencing语法报错如何解决

你遇到的报错是语法不兼容和MySQL触发器特性不匹配导致的，具体问题如下：

• 直接报错诱因：referencing是Oracle、PostgreSQL等数据库的触发器语法，MySQL原生触发器不支持该关键字，无需额外声明新行/旧行别名，直接使用内置的NEW（新增/修改后的行）、OLD（删除/修改前的行）关键字即可操作行数据。
• 其他语法问题：MySQL不支持在触发器定义的FOR EACH ROW后加WHEN条件子句，所有判断逻辑需要写在BEGIN ... END代码块内部。
• 逻辑合理性问题：你使用了AFTER INSERT触发时机，此时新增的Department数据已经写入表中，再执行回滚逻辑不合理，且MySQL触发器中直接使用ROLLBACK不符合语法规范，需要用SIGNAL语句抛出自定义错误终止操作。
• 额外逻辑提示：你当前的外键配置已经实现了「插入/修改Instructor数据时，Deptname必须在Department表中存在」的参照完整性约束，你编写的触发器逻辑是反过来要求「插入Department时Deptname必须已经在Instructor表中存在」，如果是业务需求可以保留，否则可以直接去掉触发器，仅靠外键就能满足常规参照完整性要求。

如果要实现你原来的业务逻辑，正确代码如下：

-- 临时修改语句分隔符，避免代码块内的分号被当成触发器语句的结束符
DELIMITER //
CREATE TRIGGER dept_name BEFORE INSERT ON Department
FOR EACH ROW
BEGIN
  -- 判断新插入的Deptname是否不在Instructor表中
  IF NEW.Deptname NOT IN (SELECT DISTINCT Deptname FROM Instructor) THEN
    -- 抛出自定义错误，错误码45000是用户自定义错误的通用区间
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入的部门名称不在教师表中，无法新增';
  END IF;
END //
-- 恢复默认分隔符
DELIMITER ;

注意：如果你的Instructor表当前没有数据，这个触发器会阻止所有Department表的插入操作，而你的外键约束又要求Instructor的Deptname必须存在于Department表，会出现逻辑死锁，建议根据实际业务调整校验规则，比如允许Department先创建，只通过外键约束Instructor的部门合法性即可。

内容的提问来源于stack exchange，提问作者Kushagra Agrawal