Re: trigger self recursion

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(at)svana(dot)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.