agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: intmail01 <intmail01@gmail.com>
To: Rob Sargent <robjsargent@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: How to sort deleted rows with trigger. Some rows before then some rows after.
Date: Mon, 20 Sep 2021 18:53:03 +0300
Message-ID: <bcddros62v36q1vuc6ukcm3v.1632153025543@email.android.com> (raw)
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
>
>
>
view thread (5+ messages) latest in thread
Message-ID: <bcddros62v36q1vuc6ukcm3v.1632153025543@email.android.com>
Permalink: ../bcddros62v36q1vuc6ukcm3v.1632153025543@email.android.com/
Also on: postgresql.org/message-id/bcddros62v36q1vuc6ukcm3v.1632153025543@email.android.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: intmail01@gmail.com, robjsargent@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: How to sort deleted rows with trigger. Some rows before then some rows after.
In-Reply-To: <bcddros62v36q1vuc6ukcm3v.1632153025543@email.android.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox