MySql触发器删除同一表中的子记录 [英] MySql Triggers to delete child records in the same table

问题描述

我有一个表，用于在其中存储父记录和子记录.

I have a table that stores parent and child records in it.

我试图创建一个触发器，该触发器将在删除父级时删除所有子级记录:

I was trying to create a trigger that would delete all child records when the parent is deleted:

Delete From tbl Where ParentId = OLD.Id

虽然我可以成功保存触发器，但是在删除时出现此错误:

While I can save the trigger successfully, when deleting I get this error:

错误1442:无法更新存储函数/触发器中的表'tbl'，因为调用该语句的语句已使用该表

ERROR 1442: Can’t update table ‘tbl′ in stored function/trigger because it is already used by statement which invoked this

我在做什么错了?

推荐答案

看来这是

您不能删除表中的行 激活的触发器.

You cannot DELETE rows in the table that activated trigger.

您可能会考虑的其他一些选择:

Some other options you might think about:

1. 编写删除父行和子行的应用程序逻辑，并在每次要删除父记录而不是直接删除父记录时调用此应用程序逻辑.
2. 在同一张表上的级联删除关系，可能.
3. 一个清理过程，通常清除孤儿记录.
4. (由@Chris建议)通过添加另一个表将子记录与父记录分开.

1. Write application logic that deletes the parent and child rows, and call this application logic whenever you want to delete a parent record, instead of deleting it directly.
2. Cascade delete relationship on the same table, which appears to be possible.
3. A cleanup process that routinely clears out orphaned child records.
4. (suggested by @Chris) Separate out the child records from the parent records by adding another table.

这篇关于MySql触发器删除同一表中的子记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！