Reply-To: intmail01@gmail.com
To: pgsql-sql@lists.postgresql.org
From: "intmail01@gmail.com" <intmail01@gmail.com>
Subject: How to sort deleted rows with trigger. Some rows before then some
 rows after.
Message-ID: <0c7ae95d-6c0f-88ac-1e00-a689531e34b3@gmail.com>
Disposition-Notification-To: "intmail01@gmail.com" <intmail01@gmail.com>
Date: Sun, 19 Sep 2021 17:31:04 +0000
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Content-Language: fr
Archived-At: 
 <https://www.postgresql.org/message-id/0c7ae95d-6c0f-88ac-1e00-a689531e34b3%40gmail.com>
Precedence: bulk

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