By: Ranga Babu | Updated: 2017-04-17 | Comments (2) | Related: > Replication
Problem
When creating a SQL Server trigger, one of the options is setting not for replication. This determines whether this trigger is enabled and enforced for replication subscribers. By default this option is disabled, so to enable this setting we need to specify the "NOT FOR REPLICATION" hint while creating the trigger or altering the trigger. We had a scenario to change this property for all the triggers of a database, but altering each trigger with "NOT FOR REPLICATION" enabled is a manual task which takes a lot of time. Below is an automated solution with an application to do this for all triggers.
Solution
In our scenario we have more than 1000 triggers where we needed to enable "NOT FOR REPLCIATION". I generated a script of triggers in the database and tried to edit them using find and replace, but there are several different formats like below which made this task take more time than expected and also prone to errors.
- FOR INSERT
- FOR UPDATE FOR DELETE
- FOR INSERT, DELETE
- FOR DELETE, UPDATE
- FOR INSERT, DELETE, UPDATE
For identity columns, we have an option to set "NOT FOR REPLCIATION" using the system stored procedure sys.sp_identitycolumnforreplication, but we don't have a system procedure to update "NOT FOR REPLICATION" for triggers.
We made a simple tool using the SQL Server Management Objects and we got sample code from stackoverflow by "Raf" and customized it to our needs. We created an executable, so we can execute it without using Visual Studio.
Checking Not For Replication Trigger Settings
Login to SQL Server using SSMS and use the following query to check the not for replication property of triggers.
select name,is_not_for_replication from sys.triggers where is_ms_shipped = 0
Solution to Make Changes for All Triggers
Download the zip file and extract the contents.
Double click on UpdateTriggerProperty.exe and input the instance name and database name and login credentials (this does not support Windows authentication). Check the Not For Replication box to enable the property on triggers and run it. If you want to make the change the opposite way, don't check this box.
With this setting enabled, triggers are not fired when the replication agent does insert, update or delete operations on table when this option is enabled on a trigger.
Please note that it does not update system triggers.
Checking Not For Replication Trigger Settings
Now check the not for replication property for the triggers again using the same query we used above. We can see this option is now enabled for all of the triggers.
Application Code
Below is the code that was used to build this application.
namespace UpdateTriggerProperty
{
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Threading;
///
/// Interaction logic for MainWindow.xaml
///
public partial class MainWindow : Window
{
string sqlServerLogin = string.Empty;
string password = string.Empty;
string remoteServerName = string.Empty;
string dbName = string.Empty;
bool notForReplication = false;
public MainWindow()
{
InitializeComponent();
}
private void RunButton_Click(object sender, RoutedEventArgs e)
{
try
{
ExtractData();
}
catch (Exception ex)
{
LogErrorMessage(ex.Message);
}
}
private void ExtractData()
{
try
{
sqlServerLogin = SqlUserName.Text;
password = SqlPassword.Password;
remoteServerName = RemoteServerName.Text;
dbName = DatabaseName.Text;
notForReplication = Convert.ToBoolean(NotForReplication.IsChecked);
Process();
}
catch (Exception ex)
{
LogErrorMessage(ex.Message);
}
}
private void Process()
{
Task.Factory.StartNew(() =>
{
try
{
ServerConnection conn = new ServerConnection(remoteServerName)
{
LoginSecure = false,
Login = sqlServerLogin,
Password = password,
};
Server srv = new Server(conn);
var db = srv.Databases[dbName];
foreach (Table tab in db.Tables)
{
foreach (Microsoft.SqlServer.Management.Smo.Trigger trig in tab.Triggers)
{
if (!trig.IsSystemObject)
{
trig.TextMode = false;
trig.NotForReplication = notForReplication;
trig.TextMode = true;
trig.Alter();
OutputBox.Dispatcher.BeginInvoke(DispatcherPriority.Background, new Action(() => OutputBox.Text = trig.Name));
}
}
}
}
catch (Exception ex)
{
LogErrorMessageAsync(ex.Message);
}
});
}
private void LogErrorMessage(string message)
{
OutputBox.Text = string.Empty;
OutputBox.Text = "An Error Occured\n";
OutputBox.Text += message;
}
private void LogErrorMessageAsync(string message)
{
Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Background, new Action(() =>
{
OutputBox.Text = string.Empty;
OutputBox.Text = "An Error Occured\n";
OutputBox.Text += message;
}));
}
}
}
About the code
- It opens a connection to SQL Server and it pulls all triggers for each table and checks whether it a system trigger or not. If it not a system trigger them it updates the "NOT FOR REPLICATION" value of the trigger.
- This is a basic application to update the "NOT FOR REPLICATION" value of a trigger. You can modify and extend it to fit your requirements.
- You cannot run this on subscriber database when the table is part of replication. If you do you will get an error.
Next Steps
- Download the application zip file
- Test this application in a TEST environment first to understand how this works before running on your PRODUCTION environment.
- Read these additional tips about Replication.
- Read this tip - Change Not For Replication Value for SQL Server Identity Columns.
About the author
Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-04-17
