From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
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. |
Date: | 2021-09-19 18:00:33 |
Message-ID: | CA9A2B82-E7A9-4894-B882-8253E4879279@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> 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 | intmail01 | 2021-09-20 15:53:03 | Re: How to sort deleted rows with trigger. Some rows before then some rows after. |
Previous Message | intmail01@gmail.com | 2021-09-19 17:31:04 | How to sort deleted rows with trigger. Some rows before then some rows after. |