agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedHow to sort deleted rows with trigger. Some rows before then some rows after.
5+ messages / 4 participants
[nested] [flat]
* How to sort deleted rows with trigger. Some rows before then some rows after.
@ 2021-09-19 17:31 intmail01@gmail.com <intmail01@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: intmail01@gmail.com @ 2021-09-19 17:31 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to sort deleted rows with trigger. Some rows before then some rows after.
@ 2021-09-19 18:00 Rob Sargent <robjsargent@gmail.com>
parent: intmail01@gmail.com <intmail01@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2021-09-19 18:00 UTC (permalink / raw)
To: intmail01@gmail.com; +Cc: pgsql-sql@lists.postgresql.org
> 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
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to sort deleted rows with trigger. Some rows before then some rows after.
@ 2021-09-20 15:53 intmail01 <intmail01@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: intmail01 @ 2021-09-20 15:53 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
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
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to sort deleted rows with trigger. Some rows before then some rows after.
@ 2021-09-20 16:22 David G. Johnston <david.g.johnston@gmail.com>
parent: intmail01 <intmail01@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2021-09-20 16:22 UTC (permalink / raw)
To: intmail01 <intmail01@gmail.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
On Mon, Sep 20, 2021 at 8:54 AM intmail01 <intmail01@gmail.com> wrote:
>
> How to use transaction in a trigger ? It seems there is no possibility to
> control transaction within function
>
>
You cannot. You don't get to introduce a transaction boundary in the
middle of a statement's execution. Not to mention what it would mean to
have transaction boundaries for deferred triggers.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to sort deleted rows with trigger. Some rows before then some rows after.
@ 2021-09-20 16:58 Rob Sargent <robjsargent@gmail.com>
parent: intmail01 <intmail01@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2021-09-20 16:58 UTC (permalink / raw)
To: intmail01 <intmail01@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
On 9/20/21 9:53 AM, intmail01 wrote:
>
> How to use transaction in a trigger ? It seems there is no possibility
> to control transaction within function
>
>
>
>
You would start a transaction, send two SQL statements. The trigger is
within the transaction (and does not test for negative values).
> 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
> >
> >
> >
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-09-20 16:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-19 17:31 How to sort deleted rows with trigger. Some rows before then some rows after. intmail01@gmail.com <intmail01@gmail.com>
2021-09-19 18:00 ` Rob Sargent <robjsargent@gmail.com>
2021-09-20 15:53 Re: How to sort deleted rows with trigger. Some rows before then some rows after. intmail01 <intmail01@gmail.com>
2021-09-20 16:22 ` David G. Johnston <david.g.johnston@gmail.com>
2021-09-20 16:58 ` Rob Sargent <robjsargent@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox