删除MySql前触发

2022-01-01 00:00:00 mysql triggers

我有一个名为 user 的表.这个表有一个部门表的外键.一个用户可以与一个部门相关联.在删除部门之前，我想将任何用户(具有该部门 ID)设置为默认值 (1) 以避免引用完整性错误.

I have a table named user. This table has a foreign key to a department table. One user can be associated with one department. Before deleting a department, I would like to set any user (having that department ID) to a default value (1) to avoid a referential integrity error.

你知道一个很好的例子吗?大多数示例表明触发器应用于一张表.这里触发器应该在部门上触发，但更改用户表中的值.

Do you know a good example. Most examples shows that the trigger is applied to one table. Here the trigger should be triggered on department but change values in user table.

谢谢.

推荐答案

我还没有测试过，但是 基于文档，这看起来是正确的:

I haven't tested it, but based on the documentation, this looks about right:

CREATE TRIGGER update_user_before_delete BEFORE DELETE ON department
  FOR EACH ROW BEGIN
    UPDATE user SET department = 1 WHERE department = OLD.department;
  END;

相关文章