From: | intmail01 <intmail01(at)gmail(dot)com> |
---|---|
To: | Rob Sargent <robjsargent(at)gmail(dot)com> |
Cc: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to sort deleted rows with trigger. Some rows before then some rows after. |
Date: | 2021-09-20 15:53:03 |
Message-ID: | bcddros62v36q1vuc6ukcm3v.1632153025543@email.android.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
How to use transaction in a trigger ? It seems there is no possibility to control transaction within function
-------- Original message --------
From: Rob Sargent <robjsargent(at)gmail(dot)com>
Date: 19/09/2021 21:00 (GMT+03:00)
To: intmail01(at)gmail(dot)com
Cc: pgsql-sql(at)lists(dot)postgresql(dot)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(at)gmail(dot)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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-09-20 16:22:30 | Re: How to sort deleted rows with trigger. Some rows before then some rows after. |
Previous Message | Rob Sargent | 2021-09-19 18:00:33 | Re: How to sort deleted rows with trigger. Some rows before then some rows after. |