How to check NOT FOR REPLICATION is enabled for a trigger

When creating a trigger, we can add NOT FOR REPLICATION to prevent chnaged data to be replicated to subscribers twice. In SQL Server 2005, in the view sys.triggers, the column is_not_for_replication tells you this information. However, in SQL Server 2000, there is no method (systables, sps, objectproperty etc) provided to check a trigger has "NOT FOR REPLICATION" or not without viewing the source code.
Actually the information is in the status column in the sysobjects table. We can find the number by changing a trigger with/without "NOT FOR REPLICATION" and checking the difference of the status.
Let’s create the following test table:
CREATE TABLE Test (C1 int not null, C2 int)
GO
Create an insert (or delete/update) after trigger
CREATE TRIGGER TR_Test_Insert ON Test FOR INSERT NOT FOR REPLICATION
AS
BEGIN
 DECLARE @N int
 SELECT @N=ISNULL(COUNT(*),0) FROM inserted
 IF @N>0
 PRINT CAST(@N as nvarchar(10)) +’ rows are inserted!’
END
GO
insert test (C1,C2) VALUES (1,2)
GO
SELECT status FROM sysobjects WHERE parent_obj=object_id(‘Test’) and xtype=’TR’
The status number on my box is 1610617856
GO
ALTER TRIGGER TR_Test_Insert ON Test FOR INSERT –NOT FOR REPLICATION
AS
BEGIN
 DECLARE @N int
 SELECT @N=ISNULL(COUNT(*),0) FROM inserted
 IF @N>0
 PRINT CAST(@N as nvarchar(10)) +’ rows are inserted!’
END
GO
SELECT status FROM sysobjects WHERE parent_obj=object_id(‘Test’) and xtype=’TR’
Now the number changed to 1610613760.
The difference is 1610617856- 1610613760=4096
 
Now let’s try a instead of trigger:
CREATE TRIGGER TR_Test_DeleteInsteadOf ON Test INSTEAD OF DELETE NOT FOR REPLICATION
AS
BEGIN
 UPDATE A SET C2=0 FROM Test a,deleted b WHERE a.C1=b.C1
END
GO
DELETE Test
select * FROM test
SELECT * FROM sysobjects WHERE parent_obj=object_id(‘Test’) and xtype=’TR’ and name=’TR_Test_DeleteInsteadOf’
The status on my box is 1610625280
Changed the trigger:
ALTER TRIGGER TR_Test_DeleteInsteadOf ON Test INSTEAD OF DELETE –NOT FOR REPLICATION
AS
BEGIN
 UPDATE A SET C2=0 FROM Test a,deleted b WHERE a.C1=b.C1
END
GO
SELECT status FROM sysobjects WHERE parent_obj=object_id(‘Test’) and xtype=’TR’ and name=’TR_Test_DeleteInsteadOf’
The number now is 1610621184. The difference still 4096.
 
So for a trigger the flag for "NOT FOR REPLICATION" is 4096 in status column in sysobjects. If the status&4096=4096 in sysobjects table, the trigger has NOT FOR REPLICATION, otherwise it does not have.
 
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s