Re: implicit transaction changes trigger behaviour

On Thu, Aug 29, 2019 at 2:16 PM Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:
> delete from b;
> --DELETE 3

Here the trigger is fired 3 times (for each row), and on every single
test it finds a row in 'a', that is your variable n_b_type1 is always
1, that causes the trigger (fired on each row) to not abort. If you
delete first the row that makes the trigger fail, you will not be able
to do the deletion happen outside an explicit transaction:

testdb=# delete from b where type = 1;
DELETE
testdb=# delete from b;
ERROR: Each record of a must have exactly 1 corresponding records in
b of type 1. But after this delete the a-record with id 5 would have 0
b-records of type 1, so the operation has been cancelled.

So it seems to me a problem within the trigger: when executing outside
the transaction your row is deleted as last, and this makes the
deletion "iterate" and remove all the rows. Within the transaction,
when the trigger fires, no rows are there, so it fails. Either this is
what you have to do or your query within the trigger is wrong.

Luca