由于级联触发器更新该行，因此未删除该行 [英] Row is not being deleted because of cascade trigger updates this row

问题描述

我在删除具有触发器的表中的行时遇到问题，该触发器调用第二个表的触发器，后者会更新第一个表中的行。描述如下：

I have problem with deleteing rows in table which has trigger which invokes trigger of second table, which updates row in first table. Here is the description:

Table A (id,b_table_count) 
Table B (id,a_table_id_fk)

表A触发了DELETE DELETE操作，并具有以下说明：

Table A has trigger BEFORE DELETE which has instructions:

BEGIN  
DELETE FROM b where a_table_fk = OLD.id;  
RETURN OLD;  
END;

表B触发了AFTER DELETE，指令如下：

Table B has trigger AFTER DELETE with instruction:

UPDATE a SET b_table_count = b_table_count-1 WHERE OLD.a_table_id_fk = a.id;  

当我从表A中删除行时，表B中没有连接的行，一切都正确。 br>
但是，当我从连接了表B中的行的表A中删除行时，DELETE语句返回查询成功返回：受影响的0行。我必须第二次执行DELETE语句，然后最后删除该行。在第一次DELTE之后，表B中只删除了已连接的行，但表A中被删除的行仍然保留。

When I delete row from table A, which has no connected rows in B, everything is correct.
But, when I delete row from table A, which has connected row(s) in table B, then DELETE statement returns "Query returned successfully: 0 rows affected" . I must execute DELETE statment second time, then row is finnaly deleted. After first DELTE only connected ROWS are deleted in TABLE B, but row being deleted in table A remains.

您对此有答案吗？我怀疑pgsql不允许更新触发器中要删除的行，但是我在pgsql文档中没有找到有关此行的任何信息。
解决方案是什么？

Do you have answer for that? I suspect that pgsql doesn't allow to update row being deleted in trigger, but I haven't found anything about it in pgsql documentation. What is the solution?

推荐答案

我遇到了类似的问题，但是只有一张桌子。从给定表中删除行会触发一个触发器（在删除之前），该触发器将在同一表中（根据给定条件）搜索相关行，如果找到它们，这些行将被更新。现在，如果要使用相同的delete命令删除更新的行，则不会删除该行。

I'm having a similar problem, but with one table only. Deleting rows from the given table fires a trigger (before delete), that searches for related rows int the same table (by a given condition), and if finds them, these rows get updated. Now, if an updated row is to be deleted by the same delete command, then it doesn't gets deleted.

因此，基本上，如果您创建触发器，它始终会更新要删除的同一行，因此您从现在起就无法删除任何内容。

我不知道这是否在目的，还是没有。从某种意义上讲，这是肯定的。如果您更新一条记录，则该记录与要删除的记录不同。

I don't know if this is on purpose, or not. From one way, it seems logical, that's for sure. If you update a record, it's not the same record that was intended for deletion.

（对不起，英语不好）

这篇关于由于级联触发器更新该行，因此未删除该行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！