Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Change Not For Replication for All Triggers in a SQL Server Database


By:   |   Read Comments (2)   |   Related Tips: More > Replication

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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

check the not for replciation property of SQL Server triggers

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.

Update Trigger Property Login Screen

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.

Is Not for Replicatoin is Enabled for all SQL Server 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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ranga Babu Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, April 24, 2017 - 11:46:07 AM - Scott Back To Top

This is a good idea, SMO is definately the best way to go to change this property.

But you might consider doing it in PowerShell, so there is no need for Visual Studio or for downloading strange exe's from the internet.


Tuesday, April 18, 2017 - 2:49:49 PM - Yelender Back To Top

Hi Ranga,

Thanks for the article. It is really very much useful for me. Please post more articles for automation to reduce manual effort.

Thank you.


Learn more about SQL Server tools