Writing a Replication Script

A script that is defined to run when a specific event occurs during replication. Replication scripts can use:

EITHER Standard Visual Basic .NET functions and Microsoft.VisualBasic Namespace Functions OR Standard C# functions
User functions defined in a global script
Specific replication script events, methods and properties.

Use the Replication Script Editor to write a script that is run when a specific event occurs during replication. The script should consist of one or more functions selected from the drop-down lists at the top of the Editor.

Select the language for the script (C# or VB.NET) in the Global Script Editor available in the Metadata Explorer from the metadata right mouse button menu. The default language for new scripts is C#.
In the Metadata Explorer, select the replication for which you want to write a script.
From the right mouse button menu, choose Replication Properties.
On the General tab, check the Use Script option.
Click the Script button.
The Replication Script Editor opens to display a stub for the script:

    using System;
    using System.Data;
    using DBMotoPublic;
    using DBMotoScript;
    namespace DBRS
    public class GlobalScript : IGlobalScript
    {
    }
    public class MappingRule : IMappingRule
    {
    }
    public class GlobalEvents : IGlobalEvents
    {
    }
}

From the left drop-down list, select a category to specify when the script should be run:

Category	Events	Event Type	Explanation
Refresh	onBeforeTruncate onAfterTruncate onBeforeRefresh onAfterRefresh onPrepareRefresh	Writer Writer Writer Writer Reader	Available events apply to refresh replications only.
LogReader	onPrepareMirroring onBeforeMirroring onAfterMirroring onReceiverChanged	Reader Reader Reader Reader	Available events apply only to mirroring and synchronization replications. onReceiverChanged applies only to replications involving System i/iSeries/AS400.
Record	onBeforeMapping onAfterMapping onBeforeExecute onAfterExecute	Reader Reader Writer Writer	Available events apply to each record that is considered for replication. In the case of refresh replications, the events would apply to every record in the source/target table. In the case of mirroring and synchronization replications, the events would apply to records found in the transaction log.
Replication	onConflict onLateConflict	Reader Reader	onConflict and onLateConflict should be used to resolve conflicts only for synchronization replications.

From the right drop-down list, select an event for which you want to write a script.
This drop-down list displays events appropriate for the function you selected above. If no function was selected, the list remains empty.
Events are always added directly below the ReplicationScript class as in the example below.

using System;
using System.Data;
using DBMotoPublic;
using DBMotoScript;
namespace DBRS
{
   public class ReplicationScript : IReplicationScript
   {
    public overloads void Record_onAfterMapping(DBMotoPublic.IRecord recSource, DBMotoPublic.IRecord recTarget, 
			 ref bool AbortRecord)
    }
}

Type the script in the editor pane.
The Replication Script Editor provides keyboard shortcuts for most editing needs.
If you want the behavior of your script to depend on the type of SQL operation that is being performed during replication (INSERT, UPDATE or DELETE), be sure to specify the operation type as in the example below.

using System;
using System.Data;
using DBMotoPublic;
using DBMotoScript;    
namespace DBRS
  {
   public class ReplicationScript : IReplicationScript
     {
      public override void Record_onAfterMapping(DBMotoPublic.IRecord recSource, 
	       DBMotoPublic.IRecord recTarget, ref bool AbortRecord)
         {
            switch(recTarget.OperationType)
               {
                  case enmOperationType.Insert:
                    if (recTarget.GetValueAfter("SID") == null)
                       {
                          recTarget.SetValueAfter("NAME", null);
                        }
                    else
                        { 
	                    //UpdateSTUDENTFields 
			    (recTarget);
                        }
                        break;
                 case enmOperationType.Update:
                     if (recTarget.GetValueAfter("SID") == null)
                         {
                            recTarget.SetValueAfter("NAME", null);
                         }
                     else if(recTarget.GetValueBefore("SID") == null)
                         {
                            //Field SID was null and now it has a value 
                            //UpdateSTUDENTFields 
	                     (recTarget);
			  }
                     else if(recTarget.GetValueBefore("SID") != recTarget.GetValueAfter("SID"))
                         {
			     //Field SID has changed
                            //UpdateSTUDENTFields 
	                     (recTarget);
			  }
                     break;
                 } 
              }    
        }
   }

Use IRecord methods to access values in the source and target records. Be sure to use only those methods which make sense for the operation type (INSERT, UPDATE or DELETE).
If you are using C#, to use a function already defined in a global script (or one of the predefined global script functions, precede the function name with "GlobalScript." to identify the class where the function is defined:

     GlobalScript.AddLog("The current record has been inserted: " + s, 0);

Typically, only those columns that have been mapped for replication (during replication setup) are available for use within scripts. However, if you need to access a column value that is not mapped, you can set that column as "Use Unmapped" in the Replication Properties dialog.
Be aware of script objects that need to be accessed by more than one event and be prepared to lock an object that is accessed by both Reader script events and Writer script events. Example.
Use the Expression Generator as needed to identify functions and syntax that you can use in the script.
If you have defined functions in the Global Script Editor, the function names and prototypes will be listed under User Functions in the Expression Generator.
If you have added any libraries to the list of imports in the script, click to open the References dialog and add the path to the library.
Use the Compile button to check your script syntax.
Any script problems are displayed in a separate dialog.
Correct any syntax errors before attempting to run a replication.