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.kla...@aklaver.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.kla...@aklaver.com > > <mailto: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 <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
