Re: Statement-level trigger results in recursion

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