Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Rob Sargent <robjsargent@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: How to sort deleted rows with trigger. Some rows before then some
 rows after.
Date: Sun, 19 Sep 2021 12:00:33 -0600
Message-Id: <CA9A2B82-E7A9-4894-B882-8253E4879279@gmail.com>
References: <0c7ae95d-6c0f-88ac-1e00-a689531e34b3@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
In-Reply-To: <0c7ae95d-6c0f-88ac-1e00-a689531e34b3@gmail.com>
To: intmail01@gmail.com
Archived-At: 
 <https://www.postgresql.org/message-id/CA9A2B82-E7A9-4894-B882-8253E4879279%40gmail.com>
Precedence: bulk


> On Sep 19, 2021, at 11:30 AM, intmail01@gmail.com wrote:
>=20
> =EF=BB=BFHi,
>=20
> 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.
>=20
> 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.
>=20
> 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.
>=20
> 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
>=20
> 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();
>=20
> 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.
>=20
> Question: How to set the trigger to delete some rows before and some
> other after=20

For each batch of deletes send two delete statements in a single transaction=
. The first with negative values. The second with non-negative values.=20
> Thank you
>=20
>=20
>=20