How to use transaction in a trigger ? It seems there is no possibility to control transaction within function
-------- Original message --------
From: Rob Sargent <robjsargent@gmail.com>
Date: 19/09/2021 21:00 (GMT+03:00)
To: intmail01@gmail.com
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: How to sort deleted rows with trigger. Some rows before then some rows after.
> On Sep 19, 2021, at 11:30 AM, intmail01@gmail.com wrote:
>
> Hi,
>
> Deleting some rows in my table require some rules. Some kind of row must
> be deleted before others if not error occurs.
> It is a stock management. Calculated the remains stock must be always
> positive never negative. If I delete all rows that is marked as an
> positive input quantity then the stock will be negative. Triggers
> calculate the remaining stock each time one row is deleted. It uses "FOR
> EACH ROW" option.
>
> If someone have to delete with GUi many rows and want to avoid error, he
> will be forced to delete negative before then positive after. It is a
> wast of time because when the number of rows grows the chance to redo
> the task many times due to errors.
>
> Below is an example. If user select all rows then delete them, an error
> happen. After deleting the input quantity of 20, the first row will be
> with a stock of -5.
>
> TABLE: t_stock
> Date: Qty: Stock:
> 2021/09/19 20 20
> 2021/09/20 -5 15
> 2021/09/21 10 25
> 2021/09/22 -8 17
>
> I try to use two triggers but it does not work, the deletion start
> always with the positive quantity 20 not a negative one:
> CREATE TRIGGER delete_1 BEFORE DELETE ON t_stock FOR EACH ROW WHEN
> (old.qty<0) EXECUTE FUNCTION mainfunction();
> CREATE TRIGGER delete_2 BEFORE DELETE ON t_stock FOR EACH ROW WHEN
> (old.qty>0) EXECUTE FUNCTION mainfunction();
>
> If a use "FOR EACH STATEMENT" with the Transition Tables which can help
> to list all rows to be deleted but it is only available with "AFTER"
> operation.
>
> Question: How to set the trigger to delete some rows before and some
> other after
For each batch of deletes send two delete statements in a single transaction. The first with negative values. The second with non-negative values.
> Thank you
>
>
>
