Determines whether a user-defined trigger or stored procedure is being called in the context of a replication trigger that is used for immediate updating subscriptions. This stored procedure is executed at the Publisher on the publication database or at the Subscriber on the subscription database.
sp_check_for_sync_trigger is used in snapshot replication and transactional replication.
sp_check_for_sync_trigger is used to coordinate between replication and user-defined triggers. This stored procedure determines if it is being called within the context of a replication trigger. For example, you can call the procedure sp_check_for_sync_trigger in the body of a user-defined trigger. If sp_check_for_sync_trigger returns 0, the user-defined trigger continues processing. If sp_check_for_sync_trigger returns 1, the user-defined trigger exits. This ensures that the user-defined trigger does not fire when the replication trigger updates the table.
The following example shows code that could be used in a trigger on a Subscriber table.
DECLARE @retcode int, @trigger_op char(10), @table_id int SELECT @table_id = object_id('tablename') EXEC @retcode = sp_check_for_sync_trigger @table_id, @trigger_op OUTPUT IF @retcode = 1 RETURN
The code can also be added to a trigger on a table at the Publisher; the code is similar, but the call to sp_check_for_sync_trigger includes an additional parameter.
DECLARE @retcode int, @trigger_op char(10), @table_id int, @fonpublisher int SELECT @table_id = object_id('tablename') SELECT @fonpublisher = 1 EXEC @retcode = sp_check_for_sync_trigger @table_id, @trigger_op OUTPUT, @fonpublisher IF @retcode = 1 RETURN
sp_check_for_sync_trigger stored procedure can be executed by any user with SELECT permissions in the sys.objects system view.