triggers fire circulary exceed limit of 32

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vadim - 27 Mar 2008 09:26 GMT

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

Reply to this Message

Uri Dimant - 27 Mar 2008 10:57 GMT

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

> Hi,
>
[quoted text clipped - 57 lines]
>
> Vadim

Reply to this Message

Tom Cooper - 27 Mar 2008 16:46 GMT

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

> Hi,
>
[quoted text clipped - 57 lines]
>
> Vadim

Reply to this Message

Hugo Kornelis - 27 Mar 2008 22:58 GMT

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

Signature

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

Reply to this Message

Tom Cooper - 28 Mar 2008 01:50 GMT

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

Tom

>>The way I would probably use to fix this in this case is just to add a
>>test
[quoted text clipped - 21 lines]
> I then delete the IF ... RETURN if the trigger has to do something even
> if the firing statement affected zero rows.

Reply to this Message

Vadim - 27 Mar 2008 23:00 GMT

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

> 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
[quoted text clipped - 105 lines]
>>
>> Vadim

Reply to this Message