Encountering error when using update trigger with transactional replication

I'm using transactional replication with updatable subscription. when I add a trigger for update in one of my tables which is chosen for replication in publisher, I encounter with this error:

Maximum stored procedure, function,trigger, 
or view nesting level exceeded(limit 32)

My trigger code is

create trigger Isupdated 
    on tbl_worker开发者_如何学Go 
    for update as 
        update tbl_worker SET 
           Isup=1 
        where id= (select id from inserted)

what's wrong?

It looks like you've written a recursive (aka nested) trigger.

Perhaps the trigger is causing an update on the table, causing the trigger to be fired again?

If you post the code, that would help us explain exactly what the issue is.

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/4752/Maximum-stored-procedure-function-trigger-or-view

The above link provides a solution to trigger nesting. This can be very helpful when you already have a trigger and then replication adds another one. I like this more than combining the triggers because it doesn't force you to mix code related to functionality and code related to replication.

To sum up the solution:

You can prevent the nested firing from happening by assigning the triggers an order with sp_settriggerorder and add the following check to the beginning of the trigger you set to fire first to prevent it being fired by the other trigger:

CREATE TRIGGER.... IF TRIGGER_NESTLEVEL > 1 RETURN