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@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
