Subject Re: tricky trigger
Author martinknappe
--- In, Miroslav Penchev
<miroslavp@...> wrote:
> На Mon, 31 Jul 2006 17:00:47 -0000
> "martinknappe" <martin@...> написа:
> > hi
> >...
> > CREATE trigger dicentries_bu0 for dicentries
> > active before update position 0
> > as
> > declare variable wanted_id bigint;
> > begin
> > for select id from dicentries where asverwid =
> > into :wanted_id do begin
> > update dicentries
> > set
> > asverwterm = new.asterm
> > where
> > ID = :wanted_id;
> > end
> > end
> >
> >
> > does any of you see why it doesnt work?
> >
> I do not know what you have in mind with "it doesnt work", but your
> trigger will fire infinite loop of BEFORE UPDATE trigger.
> Cheers,
> --
> Miroslav Penchev

No, it will not. Theres one thing I forgot to mention: I have 2
different kinds of entries: main entries and side entries.
An entry is a main entry if asverwid AND asverwterm are NULL.
A side entry (used for synonyms in the dictionary) has asverwid AND
asverwterm NOT NULL.
what does that mean? a side entry saves in asverwid the id of the one
entry which it is a synonym of. likewise, it saves in asverwterm the
asterm field of the one entry which it is a synonym of.
so every time i update an entry i have to check whether the updated
entry is a main entry and if so, asverwterm of all its synonyms has to
be updated because asterm may have been updated, potentially leading
to an inconsistency in the table.
so you're right, the trigger will itself prompt a before update event
for all the synonyms of the updated entry but BECAUSE a synonym (side
entry) can by definition NOT be the main entry of yet another synonym,
no further before-updates are generated.