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 (); Thanks and regards, Jiten On Mon 18 Feb, 2019, 9:32 PM Adrian Klaver, <adrian.kla...@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.kla...@aklaver.com >
