MySQL 删除MySql前触发
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1948047/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trigger before delete MySql
提问by rtacconi
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.
我有一个名为 user 的表。这个表有一个部门表的外键。一个用户可以与一个部门相关联。在删除部门之前，我想将任何用户（具有该部门 ID）设置为默认值 (1) 以避免引用完整性错误。
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.
你知道一个很好的例子。大多数示例表明触发器应用于一张表。这里触发器应该在部门上触发，但更改用户表中的值。
Thanks.
谢谢。
回答by Andy West
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;
回答by Ike Walker
In most cases it is better to set the child value to NULL when the parent is deleted, rather than using a default of 1 like you are doing.
在大多数情况下，最好在删除父项时将子值设置为 NULL，而不是像您这样做使用默认值 1。
If you decide that this behavior is appropriate, then you can make it an attribute of the foreign key, and won't require a trigger at all.
如果您认为这种行为是合适的，那么您可以将其设为外键的一个属性，而根本不需要触发器。
Something like this:
像这样的东西：
ALTER TABLE `user`
ADD CONSTRAINT FK_USER_TO_DEPARTMENT FOREIGN KEY (department_id)
REFERENCES `department` (department_id) ON DELETE SET NULL;
回答by Stefan
You can use any sql statement in your trigger code. When a record is deleted the trigger-code is fired. You can use the record wich fired the trigger (to select the department id) and then select any user with that id and update that user record. Good luck
您可以在触发器代码中使用任何 sql 语句。当记录被删除时，触发代码被触发。您可以使用触发触发器的记录（选择部门 ID），然后选择具有该 ID 的任何用户并更新该用户记录。祝你好运
