Thread: BEFORE ... Statement-level trigger

BEFORE ... Statement-level trigger

From
Jitendra Loyal
Date:
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. 

Thanks
Jiten

Re: BEFORE ... Statement-level trigger

From
Adrian Klaver
Date:
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


Re: BEFORE ... Statement-level trigger

From
Jitendra Loyal
Date:
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 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

Re: BEFORE ... Statement-level trigger

From
Adrian Klaver
Date:
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


Re: BEFORE ... Statement-level trigger

From
Jitendra Loyal
Date:
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

Re: BEFORE ... Statement-level trigger

From
Adrian Klaver
Date:
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


Re: BEFORE ... Statement-level trigger

From
Jitendra Loyal
Date:
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