PostgreSQL Discussion - trigger self recursion

Author

trigger self recursion

Sim Zacks

2005-09-28, 7:23 am

Is there a way to avoid trigger self-recursion?
In other words, update a table and have the trigger update another row in
the same table without calling the same trigger?

Martijn van Oosterhout

2005-09-28, 7:23 am

On Wed, Sep 28, 2005 at 01:41:29PM +0200, Sim Zacks wrote:
> Is there a way to avoid trigger self-recursion?
> In other words, update a table and have the trigger update another row in
> the same table without calling the same trigger?

No, although generally it's a sign of a coding problem. If you're
trying to change values being updated, you should be assigning to NEW,
not executing another UPDATE. If you're really recursing, there should
be an obvious way to know where you're done...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Richard Huxton

2005-09-28, 7:23 am

Sim Zacks wrote:
> Is there a way to avoid trigger self-recursion?
> In other words, update a table and have the trigger update another row in
> the same table without calling the same trigger?

No a per-row trigger will fire for every row updated.

There is presumably some test you can make to see whether your trigger
should do any work, or just exit. I have seen people on the list who
wrote the test in "C" and put the work in a separate function, but have
never needed that level of complexity myself.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sim Zacks

2005-09-28, 7:23 am

Basically I have a table that is not fully normalized. When the user
updates a field that has a "duplicate" I would like it to update those
duplicate rows as well.
The code is very straightforward.

Update table1 set f1=new.f1,f2=new.f2,f3=new.f3 where pk<>new.pk
and f4=new.f4 and f5=new.f5

Where table1 is the original table being updated.

There are a couple of fields unique for each row so they are not
complete duplicates. I can't normalize the tables right now because
that would break the application. It was originally normalized because
the business rules said we didn't allow duplicates. As always, the
business rules changed and we let duplicates in, but that makes
maintainability harder because when an attribute is changed in one it
has to be changed in all of them.
Now until I can get the application to work with normalized tables, I
would like a trigger to provide the maintainability.

In SQL Server/Sybase, for example, a trigger is only fired per table
once.

Sim
____________________
____________________
____________________
____________________

On Wed, Sep 28, 2005 at 01:41:29PM +0200, Sim Zacks wrote:
> Is there a way to avoid trigger self-recursion?
> In other words, update a table and have the trigger update another row in
> the same table without calling the same trigger?

No, although generally it's a sign of a coding problem. If you're
trying to change values being updated, you should be assigning to NEW,
not executing another UPDATE. If you're really recursing, there should
be an obvious way to know where you're done...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Martijn van Oosterhout

2005-09-28, 7:23 am

On Wed, Sep 28, 2005 at 02:20:57PM +0200, Sim Zacks wrote:
> Basically I have a table that is not fully normalized. When the user
> updates a field that has a "duplicate" I would like it to update those
> duplicate rows as well.
> The code is very straightforward.
>
> Update table1 set f1=new.f1,f2=new.f2,f3=new.f3 where pk<>new.pk
> and f4=new.f4 and f5=new.f5
>
> Where table1 is the original table being updated.

Well, the solution seems to me to be:

Update table1 set f1=new.f1,f2=new.f2,f3=new.f3 where pk<>new.pk
and f4=new.f4 and f5=new.f5 and (f1<>new.f1 or f2<>new.f2 or f3<>new.f3);

I.e., say what you mean. You don't want to update rows that already
have the right values.

> In SQL Server/Sybase, for example, a trigger is only fired per table
> once.

Once per row I assume. If you're updating multiple rows you want the
trigger to apply to each change. Seems like an arbitrary restriction to
me.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Sim Zacks

2005-09-28, 9:23 am

Good point. I don't know how I missed that one.

Thank You
Sim

____________________
____________________
____________________
____________________

On Wed, Sep 28, 2005 at 02:20:57PM +0200, Sim Zacks wrote:
> Basically I have a table that is not fully normalized. When the user
> updates a field that has a "duplicate" I would like it to update those
> duplicate rows as well.
> The code is very straightforward.
>
> Update table1 set f1=new.f1,f2=new.f2,f3=new.f3 where pk<>new.pk
> and f4=new.f4 and f5=new.f5
>
> Where table1 is the original table being updated.

Well, the solution seems to me to be:

Update table1 set f1=new.f1,f2=new.f2,f3=new.f3 where pk<>new.pk
and f4=new.f4 and f5=new.f5 and (f1<>new.f1 or f2<>new.f2 or f3<>new.f3);

I.e., say what you mean. You don't want to update rows that already
have the right values.

> In SQL Server/Sybase, for example, a trigger is only fired per table
> once.

Once per row I assume. If you're updating multiple rows you want the
trigger to apply to each change. Seems like an arbitrary restriction to
me.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links