Thread: Statement-level trigger results in recursion

Statement-level trigger results in recursion

From

Jitendra Loyal

Date:

18 February 2019, 15:11:35

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;

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.

Thanks,

Jiten

Re: Statement-level trigger results in recursion

From

Adrian Klaver

Date:

18 February 2019, 19:01:55

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

Re: Statement-level trigger results in recursion

From

Jitendra Loyal

Date:

18 February 2019, 19:23:35

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.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

Re: Statement-level trigger results in recursion

From

Adrian Klaver

Date:

18 February 2019, 19:29:01

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

Re: Statement-level trigger results in recursion

From

Jitendra Loyal

Date:

18 February 2019, 19:38:00

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.klaver@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.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

Re: Statement-level trigger results in recursion

From

Adrian Klaver

Date:

18 February 2019, 19:44:03

On 2/18/19 8:38 AM, Jitendra Loyal wrote:
> Unfortunately no! Where can I see those? Will I don't my answer there; I 
> have referred to the documentation and tried various things.

Please do not top post. The style on this list is to use inline posting.

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 and regards,
> Ken i
> 
> On Mon 18 Feb, 2019, 9:59 PM Adrian Klaver, <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto: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>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Statement-level trigger results in recursion

From

Jitendra Loyal

Date:

18 February 2019, 20:07:35

I do understand that the statement level trigger will be executed once before the operation. My point is.. if one does not know the rows, what kind of use it can be put to. What is the use case? Like in after triggers, one gets the rows in transition tables, how does one do with vefore trigger.

Thanks and regards,

Jiten

Re: Statement-level trigger results in recursion

From

Adrian Klaver

Date:

19 February 2019, 00:49:28

On 2/18/19 9:07 AM, Jitendra Loyal wrote:
> I do understand that the statement level trigger will be executed once 
> before the operation. My point is.. if one does not know the rows, what 
> kind of use it can be put to. What is the use case? Like in after 
> triggers, one gets the rows in transition tables, how does one do with 
> vefore trigger.

Use FOR EACH ROW.

Why you cannot use a FOR EACH STATEMENT trigger is something I thought I 
remember being discussed on the list before. Unfortunately I cannot find 
that conversation at the moment. Someone else will need to weigh in on this.

> 
> Thanks and regards,
> Jiten

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Statement-level trigger results in recursion

From

Jitendra Loyal

Date:

19 February 2019, 02:39:16

Thanks for all your efforts. I appreciate it.

Let us wait and see if someone can enlighten us, or you locate the conversation.

Thanks once again

Regards,

Jiten

On Tue 19 Feb, 2019, 3:19 AM Adrian Klaver, <adrian.klaver@aklaver.com> wrote:

On 2/18/19 9:07 AM, Jitendra Loyal wrote:
> I do understand that the statement level trigger will be executed once
> before the operation. My point is.. if one does not know the rows, what
> kind of use it can be put to. What is the use case? Like in after
> triggers, one gets the rows in transition tables, how does one do with
> vefore trigger.

Use FOR EACH ROW.

Why you cannot use a FOR EACH STATEMENT trigger is something I thought I
remember being discussed on the list before. Unfortunately I cannot find
that conversation at the moment. Someone else will need to weigh in on this.

>
> Thanks and regards,
> Jiten

--
Adrian Klaver
adrian.klaver@aklaver.com