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

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
>

The reason this happens to you is that triggers fire even if no rows were
updated. This is by design, because, for example, you might be using the
trigger to aduit attempts to change data in a table and desire to write an
audit row with information like date and time and workstationid even if no
row was successfully updated.
So the triggers you have work like this

1) You execute an update statement on TableA that updates 5 rows
2) TableA's trigger is fired and there are 5 rows in the inserted virtual
table
3) TableA updates those 5 rows in TableB
4) TableB's trigger is fired and there are 5 rows in the inserted virtual
table
5) TableB's trigger does the update statement for TableA and that update
statement updates 0 rows since no rows in TableA match your Where clause
Here's where the trouble begins
6) TableA's trigger is fired and there are --0-- rows in the inserted
virtual table
7) TableA's trigger does the update statement for TableB and that update
statement updates 0 rows since no rows in TableB match your Where clause
8) TableB's trigger is fired and there are --0-- rows in the inserted
virtual table
9) TableB's trigger does the update statement for TableA and that update
statement updates 0 rows since no rows in TableA match your Where clause
etc until you are 32 levels deep in nested triggers and the entire update is
rolled back and an error returned.

The way I would probably use to fix this in this case is just to add a test
at the beginning of each trigger to see if the inserted table has any rows,
e.g.,
If Exists (Select * From inserted) or If Not Exists (Select * From inserted)
If there are no rows, you have nothing for this trigger to do, so just exit.
But if there are rows in inserted, then do the update of the other table.

Other possible fixes:
1) There is a built in function TRIGGER_NESTLEVEL([object_id]), which you
can use to discover how deep you are nested in triggers, see BOL for
information on this function.
2) Do all updates to these tables with stored procedures that maintain both
TableA and TableB, then you don't need triggers at all. If I were designing
this system from scratch, this is the method I would choose, but if you have
an existing system, converting to this might not be practical.

Tom

"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
>

On Thu, 27 Mar 2008 11:46:22 -0400, Tom Cooper wrote:

>The way I would probably use to fix this in this case is just to add a test
>at the beginning of each trigger to see if the inserted table has any rows,
>e.g.,
>If Exists (Select * From inserted) or If Not Exists (Select * From inserted)
(snip)

Hi Tom,

Better just test @@ROWCOUNT. Make sure it's the first statement of the
trigger, though!

I always start each trigger with

CREATE TRIGGER TriggerName
ON TableName AFTER Operation(s)
AS
IF @@ROWCOUNT = 0 RETURN;
/* Rest of the code goes here */
go

I then delete the IF ... RETURN if the trigger has to do something even
if the firing statement affected zero rows.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Tom, thank you very much for taking your time to look at my situation,
Your suggestion of checking if exists(select * from inserted) worked.
I didn't know a trigger would fire on a table if no rows were updated.

Yes, this had to be added to an existing system so triggers were the only
solution.

Vadim

"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:%23RkG1GCkIHA.5820@TK2MSFTNGP04.phx.gbl...
> The reason this happens to you is that triggers fire even if no rows were
> updated. This is by design, because, for example, you might be using the
> trigger to aduit attempts to change data in a table and desire to write an
> audit row with information like date and time and workstationid even if no
> row was successfully updated.
> So the triggers you have work like this
>
> 1) You execute an update statement on TableA that updates 5 rows
> 2) TableA's trigger is fired and there are 5 rows in the inserted virtual
> table
> 3) TableA updates those 5 rows in TableB
> 4) TableB's trigger is fired and there are 5 rows in the inserted virtual
> table
> 5) TableB's trigger does the update statement for TableA and that update
> statement updates 0 rows since no rows in TableA match your Where clause
> Here's where the trouble begins
> 6) TableA's trigger is fired and there are --0-- rows in the inserted
> virtual table
> 7) TableA's trigger does the update statement for TableB and that update
> statement updates 0 rows since no rows in TableB match your Where clause
> 8) TableB's trigger is fired and there are --0-- rows in the inserted
> virtual table
> 9) TableB's trigger does the update statement for TableA and that update
> statement updates 0 rows since no rows in TableA match your Where clause
> etc until you are 32 levels deep in nested triggers and the entire update
> is rolled back and an error returned.
>
> The way I would probably use to fix this in this case is just to add a
> test at the beginning of each trigger to see if the inserted table has any
> rows, e.g.,
> If Exists (Select * From inserted) or If Not Exists (Select * From
> inserted)
> If there are no rows, you have nothing for this trigger to do, so just
> exit. But if there are rows in inserted, then do the update of the other
> table.
>
> Other possible fixes:
> 1) There is a built in function TRIGGER_NESTLEVEL([object_id]), which you
> can use to discover how deep you are nested in triggers, see BOL for
> information on this function.
> 2) Do all updates to these tables with stored procedures that maintain
> both TableA and TableB, then you don't need triggers at all. If I were
> designing this system from scratch, this is the method I would choose, but
> if you have an existing system, converting to this might not be practical.
>
> Tom
>
> "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
>>
>
>

Thanks. You're right. Either method will work correctly, but checking
@@RowCount is a cleaner and probably more efficient way to do this.

Tom

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:i06ou3hft0v9p0nug407ta3ksh1vvhjvt9@4ax.com...
> On Thu, 27 Mar 2008 11:46:22 -0400, Tom Cooper wrote:
>
>>The way I would probably use to fix this in this case is just to add a
>>test
>>at the beginning of each trigger to see if the inserted table has any
>>rows,
>>e.g.,
>>If Exists (Select * From inserted) or If Not Exists (Select * From
>>inserted)
> (snip)
>
> Hi Tom,
>
> Better just test @@ROWCOUNT. Make sure it's the first statement of the
> trigger, though!
>
> I always start each trigger with
>
> CREATE TRIGGER TriggerName
> ON TableName AFTER Operation(s)
> AS
> IF @@ROWCOUNT = 0 RETURN;
> /* Rest of the code goes here */
> go
>
> I then delete the IF ... RETURN if the trigger has to do something even
> if the firing statement affected zero rows.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis