Vadim
Is it SS2005?
How do you perform updating? I don't think that in your case triggers is a
good way.

Anyway , if you update a column take a look at TWO virtual tables 'deleted'
an' inserted' ,perhaps you need to JOIN them on primary key



"Vadim" <vadim@dontsend.com> wrote in message
news:%23z6FeN%23jIHA.484@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> Maybe somebody can point me to a right direction on how to do this.
> Here is a simplified version of what I am trying to achieve:
>
> I have 2 tables:
>
> Table A (
> ID integer identity,
> someString Varchar(100)
> )
>
> Table B (
> ID integer identity
> tableA_ID integer references A(ID),
> someString varchar(100)
> )
>
> whenever somestring of table A updated somestring of table B should be
> also updated, using B.tableA_ID as a link
> and vice versa whenever B.somestring is updated a.somestring has to be
> updated with the same value.
>
> To do this I have 2 triggers on table A and table B
>
> create trigger trg_A
> ON A for insert,update AS
> if update(somestring)
> update B set B.somestring = inserted.somestring
> from inserted where (inserted.id = B.tablea_id) and
> (B.somestring <> inserted.somestring)
>
>
> create trigger trg_B
> ON B for insert,update AS
> if update(somestring)
> update A set A.somestring = inserted.somestring
> from inserted where (inserted.tablea_id = A.id) and
> (A.somestring <> inserted.somestring)
>
> When I update somestring of either table I get an error message about the
> triggers reaching nested level of 32 of something like that.
>
> To prevent this from happening I included
> (B.somestring <> inserted.somestring)
> and
> (A.somestring <> inserted.somestring)
>
> So from my understanding:
> it should follow this scenario:
> 1.a.somestring is updated and it fires a trigger that updates b.somestring
> 2.a trigger fires on b.somestring and tries to update a.somestring but
> because of the line:
> (A.somestring <> inserted.somestring) the update shouldn't happen since
> a.somestring has already been updated above.
>
> Sorry for the long explanation. Does anybody know why this happens and how
> to deal with this?
>
> Thank you,
>
> Vadim
>