--- In firebird-support@yahoogroups.com, "martinknappe" <martin@...>
wrote:

>
> hi
> thx for your quick replies to my other post
> here goes another problem i'm having, but first a quick brief on the
> relevant fields in my db necessary for understanding my point:
>
> my database contains - amongst others - the following table:
>
> CREATE TABLE DICENTRIES (
> ID BIGINT NOT NULL,
> ASTERM S_CHAR_80,
> ASVERWID BIGINT,
> ASVERWTERM S_CHAR_80,
> //more fields; not relevant here
> );
>
> plus the following foreign keys:
>
> ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_2 FOREIGN KEY
> (ASVERWID) REFERENCES DICENTRIES (ID) ON UPDATE CASCADE;
>
>
> as you see, asverwid references id of another entry.
> i also have a trigger that does the following: upon insert of a new
> entry, it checks whether asverwid has been assigned any value and if
> so it goes out and looks for the corresponding asterm (i.e. "where id
> = new.asverwid") and inserts this value into new.asverwterm.
>
> this all works; now comes the problem:
>
> what i also need is a trigger that upon update does this: check
> whether there are any entries where asverwid = new.id and if so,
> update the corresponding field "asverwterm"
>
> what i've written is the following trigger but it doesnt work :-(
>
> 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 = new.id 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?

Hi Martin,

You may want to clarify what you mean by "doesnt work", whether that
means you are getting an exception, whether it simply doesn't fire,
whether it freezes, whether it does the wrong thing, etc.

The first thing to realise is that you are doing something
recursively, so 'be careful' alarm bells need to be ringing. I
actually don't understand your response to Miroslav but you seem to
believe you have dealt with the case where the update causes recursion.

At the very least I would change your update statement to include an
out clause, like below:

update dicentries set
asverwterm = new.asterm
where
ID = :wanted_id
and ((asverwterm <> new.asterm) OR
((asverwterm is null) and (new.asterm is not null))).

More alarm bells need to ring when you are running batch updates of
one field based on another field in the same record. It looks like a
denormalised database structure. I am struggling to understand why you
have such a common field between multiple records of dicentries, when
you obviously have a second field asverwid. I would have thought that
asverwterm belonged to a different table that is linked via asverwid
in a normalised structure. If the structure was like this, then no
trigger would even be necessary and you would not risk additional lock
conflicts when you change a asverwterm value just because another user
is modifying a dicentries record.

Adam