Re: Statement-level trigger results in recursion

Unfortunately no! Where can I see those? Will I don't my answer there; I
have referred to the documentation and tried various things.

Thanks and regards,
Ken i

On Mon 18 Feb, 2019, 9:59 PM Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 2/18/19 8:23 AM, Jitendra Loyal wrote:
> > My bad!
> >
> > It is a transition table. Consider the following revised definition of
> > trigger:
> >
> >
> > CREATE TRIGGER storage_locations_b_u_AS_DML
> > AFTER UPDATE
> > ON storage_locations
> > REFERENCING NEW TABLE AS new_table
> > OLD TABLE AS old_table
> > FOR EACH STATEMENT EXECUTE FUNCTION TRG_storage_locations_b_u_AS_DML ();
>
> Alright I understand now.
>
> Did you see the rest of my previous post about AFTER STATEMENT running
> regardless of number of rows affected?
>
> >
> > Thanks and regards,
> > Jiten
> >
> > On Mon 18 Feb, 2019, 9:32 PM Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> >
> > On 2/18/19 4:11 AM, Jitendra Loyal wrote:
> > >
> > > The AFTER Statement-level Trigger runs into infinite execution
> when
> > > another set of rows are affected for the same table through this
> > > trigger. Consider this use case where a table storage_locations
> that
> > > manages a hierarchy of storage_locations in stores, and thus
> having
> > > following columns (for simplicity):
> > >
> > >
> > >
> > >
> > > storage_location_id SERIAL NOT NULL PRIMARY KEY,
> > > store_id INTEGER NOT NULL, -- REFERENCES stores
> > > storage_location_nm VARCHAR (25) NOT NULL,
> > > parent_storage_location_id INTEGER NULL REFERENCES
> > storage_locations,
> > > ---- NULL for root storage locations
> > > storage_location_path TEXT NOT NULL
> > >
> > >
> > >
> > >
> > >
> > > I have a BEFORE ROW trigger, which updates the
> > storage_location_path with
> > > parent's storage_location_path, if any, concatenated with its
> > > storage_location_name. This works fine - no issues.
> > >
> > > I have another AFTER UPDATE STATEMENT-level Trigger and function
> > definitions
> > > as below (which updates the storage_path of the children):
> > >
> > >
> > >
> > >
> > > CREATE FUNCTION TRG_storage_locations_b_u_AS_DML ()
> > > RETURNS TRIGGER
> > > AS $$
> > > DECLARE
> > > v_separator VARCHAR (1) = '/';
> > > v_cnt INT;
> > > BEGIN
> > > -- [ -- Required to prevent infinite recursion
> > > SELECT COUNT (*) INTO v_cnt
> > > FROM new_table;
> >
> > Where is new_table coming from?
> >
> > >
> > > IF (v_cnt > 0) THEN
> > > -- ] -- Required to prevent infinite recursion
> > > UPDATE storage_locations
> > > SET storage_location_path = COALESCE (i.storage_location_path ||
> > > v_separator, '') || storage_locations.storage_location_nm
> > > FROM inserted i
> > > JOIN deleted d
> > > ON ( i.storage_location_id = d.storage_location_id
> > > AND i.storage_location_path != d.storage_location_path
> > > )
> > > WHERE storage_locations.parent_storage_location_id =
> > i.storage_location_id;
> > > END IF;
> > > RETURN NULL;
> > > END
> > > $$ LANGUAGE plpgsql;
> > >
> > > CREATE TRIGGER storage_locations_b_u_AS_DML
> > > AFTER UPDATE
> > > ON storage_locations
> > > REFERENCING NEW TABLE AS inserted
> > > OLD TABLE AS deleted
> > > FOR EACH STATEMENT EXECUTE FUNCTION
> > TRG_storage_locations_b_u_AS_DML ();
> > >
> > > Notice that the Trigger is getting called endlessly (if the
> > number of
> > > rows in the NEW TABLE are NOT checked). I reckon if there are not
> > any
> > > rows, what is the need to call the trigger. Or, may be, I am
> missing
> > > something, which I need to learn.
> >
> > Yes:
> >
> > https://www.postgresql.org/docs/10/sql-createtrigger.html
> >
> > "... In contrast, a trigger that is marked FOR EACH STATEMENT only
> > executes once for any given operation, regardless of how many rows it
> > modifies (in particular, an operation that modifies zero rows will
> > still
> > result in the execution of any applicable FOR EACH STATEMENT
> triggers)."
> >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Thanks,
> > >
> > >
> > >
> > >
> > >
> > > Jiten
> > >
> > >
> > >
> > >
> >
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>