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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com