BEFORE ... Statement-level trigger

On 2/18/19 4:06 AM, Jitendra Loyal wrote:

The behaviour is not clear for BEFORE Statement-level Trigger. This is
because transition tables cannot be used. So how does one get access to
the rows being affected? It is not documented either.

If you need the row values then use a FOR ROW trigger.

Thanks
Jiten

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks Adrian

I am trying to understand as to how a BEFORE statement-level trigger can be
used. Since it is a trigger, one needs to know which rows are being
affected.

Regards,
Jiten

On Mon 18 Feb, 2019, 9:42 PM Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:

On 2/18/19 8:20 AM, Jitendra Loyal wrote:

Thanks Adrian

I am trying to understand as to how a BEFORE statement-level trigger can
be used. Since it is a trigger, one needs to know which rows are being
affected.

But you can't:

https://www.postgresql.org/docs/10/plpgsql-trigger.html

"NEW

Data type RECORD; variable holding the new database row for
INSERT/UPDATE operations in row-level triggers. This variable is
unassigned in statement-level triggers and for DELETE operations.
OLD

Data type RECORD; variable holding the old database row for
UPDATE/DELETE operations in row-level triggers. This variable is
unassigned in statement-level triggers and for INSERT operations.
"

Regards,
Jiten

On Mon 18 Feb, 2019, 9:42 PM Adrian Klaver, <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 2/18/19 4:06 AM, Jitendra Loyal wrote:

The behaviour is not clear for BEFORE Statement-level Trigger.

This is

because transition tables cannot be used. So how does one get

access to

the rows being affected? It is not documented either.

If you need the row values then use a FOR ROW trigger.

Thanks
Jiten

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

I have gone through the documentation quite a number of times to establish
the understanding. However, I had been wondering about the recursion in the
case I put forth.

Is there a better way to handle this requirement? The point is that the
trigger is being called when no rows are affected.

Thanks and regards,
Jiten

On 2/18/19 9:11 AM, Jitendra Loyal wrote:

I have gone through the documentation quite a number of times to
establish the understanding. However, I had been wondering about the
recursion in the case I put forth.

Is there a better way to handle this requirement? The point is that the
trigger is being called when no rows are affected.

Well as the docs say a FOR EACH STATEMENT trigger will execute in the
absence of changed rows. So you have two options:

1) Use a FOR EACH ROW trigger.

2) Do what you did and include a sanity check.

Best guess is your AFTER STATEMENT trigger is tripping your BEFORE ROW
trigger which then trips your AFTER STATEMENT and so on.

Thanks and regards,
Jiten

--
Adrian Klaver
adrian.klaver@aklaver.com

I will not prefer to use a row trigger on this case for that will be
relatively inefficient.

So can we conclude that a sanity check is essential when using statement
level trigger.

Thanks and regards,
Jiten