Обсуждение: How to sort deleted rows with trigger. Some rows before then some rows after.

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 ?

Thank you

> 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
secondwith non-negative values.  
> Thank you
>
>
>

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
>
>
>

On 9/20/21 9:53 AM, intmail01 wrote:
>
> How to use transaction in a trigger ? It seems there is no possibility 
> to control transaction within function
>
>
>
>
You would start a transaction, send two SQL statements.  The trigger is 
within the transaction (and does not test for negative values).
> 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
> >
> >
> >
>