Subject Re: tricky trigger
Author martinknappe
> I don't see why it could not work, but your select-update
> construction is completely equal to simple "UPDATE dicentries SET
> asverterm=new.asterm WHERE". May be it is not what
> you had in mind...
> --
> SY, Dimitry Sibiryakov.

Maybe I just didn't see the woods for trees; you're completely right -
this is semantically the same; I changed that. But it still doesn't
work and I mean just that: It seem it doesn't do anything. Just
imagine 2 entries like the following:

asterm: 'entry'
id: 1
asverwid = null
asverwterm = null

asterm: 'synonym'
id: 2
asverwid: 1
asverwterm: 'entry'

When I change the entry where id = 1 (say asterm = 'changed entry')
than I'd expect this trigger to update the other entry setting
asverwterm from 'entry' to 'changed entry'

I know these are redundant fields (asverwid and asverwterm) and you'd
need only one of them; the problem was just the following: just having
asverwterm is not enough as there may be more entries with the same
asterm; so I chose the id as the foreign key. but then i found i could
speed things up a little and since asverwterm is a field that's always
displayed when displaying the respective record I thought I'd just
take it up into the record as may point?

why should this not cause recursion:

when i update asterm where id = 1 to 'changed entry' then the trigger
should start to executeand look for all records where asverwid = 1: it
should find the record where id = 2 and update asverwterm to 'changed
entry'. then the trigger should start to execute again and look for
all records where asverwid = 2 but i'll find none (because a synonym
cannot have yet another synonym BY DEFINITION) so that's where the
recursion should stop; see my point?