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)

Table A has trigger BEFORE DELETE which has instructions:

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

Table B has trigger AFTER D开发者_StackOverflow中文版ELETE with instruction:

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

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.

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?

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.

So basically, if you create a trigger, that always updates the same row that is being deleted, you can't delete anything from now on.

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.

(sorry for bad english)

So basicly you have a A -> trigger -> B -> trigger -> A situation, which is actually incorrect thing to do by design and I think postgres locks the row that is executing trigger or A row is locked but i'm not sure about all inner behavior of postgres.

so you can try DELETE FROM B WHERE a_id = %Row_to_delete_id% first and then DELETE FROM A WHERE a_id = %Row_to_delete_id%, in a transaction,

but i highly recommend you revise your trigger dependencies