Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Managed Instance
Determines whether a user-defined trigger or stored procedure is being called in the context of a replication trigger, which 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.
Transact-SQL syntax conventions
sp_check_for_sync_trigger
[ @tabid = ] tabid
[ , [ @trigger_op = ] 'trigger_op' OUTPUT ]
[ , [ @fonpublisher = ] fonpublisher ]
[ ; ]
The object ID of the table being checked for immediate updating triggers. @tabid is int, with no default.
Specifies if the output parameter is to return the type of trigger it's being called from. @trigger_op is an OUTPUT parameter of type char(10), and can be one of these values.
Value | Description |
---|---|
Ins |
INSERT trigger |
Upd |
UPDATE trigger |
Del |
DELETE trigger |
NULL (default) |
Specifies the location where the stored procedure is executed. @fonpublisher is bit, with a default of 0
.
0
, the execution is at the Subscriber.1
, the execution is at the Publisher.0 indicates that the stored procedure isn't being called within the context of an immediate-updating trigger. 1 indicates that it's being called within the context of an immediate-updating trigger and is the type of trigger being returned in @trigger_op.
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's 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 doesn't 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 extra 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;
Any user with SELECT
permissions in the sys.objects system view can execute sp_check_for_sync_trigger
.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register today