Thread: trigger self recursion
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?
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.
Attachment
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
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.
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.
Attachment
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.