Обсуждение: BEFORE ... Statement-level trigger
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
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 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
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