Ignore triggers and indexes

hcripps · September 17, 2015 12:52PM

I am using vb.net program to update a database with table changes using SQL Comparison. However, if the target table has a trigger that is NOT in the source I don't want it to be deleted. I will take responsibility for managing conflicts between changes to tables and indexes/triggers.

Note: With the current version of the vb.net program, triggers and indexes are not affected if there are no changes in the table - even when the index or trigger does not exist in the source database.

I have tried Options.IgnoreTriggers and Options.IgnoreIndexes.

Can you give some ideas on how to handle this?

Here are snippets of vb.net code being used:

Dim SpecialOptions As Options = Options.Default
SpecialOptions = SpecialOptions.Plus(Options.ForceColumnOrder)
SpecialOptions = SpecialOptions.Plus(Options.IgnoreTriggers)
SpecialOptions = SpecialOptions.Plus(Options.IgnoreIndexes)
...
stagingDB.Register(sourceConnectionProperties, SpecialOptions)
...
productionDB.Register(targetConnectionProperties, SpecialOptions)
...
Dim stagingVsProduction As Differences = stagingDB.CompareWith(productionDB, SpecialOptions)
...
For Each difference In stagingVsProduction
Dim Msg As String = String.Empty
With difference
If difference.Type <> RedGate.Shared.ComparisonInterfaces.DifferenceType.Equal Then
.Selected = True
' These entities will not be deleted from the target database. For example, if a special view has been added to a client database, it won't
' be removed when syncronizing that database.
If .Type = RedGate.Shared.ComparisonInterfaces.DifferenceType.OnlyIn2 AndAlso .ObjectIn2 IsNot Nothing AndAlso
(.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.Table Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.Trigger Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.View Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.StoredProcedure Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.Schema Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.Function Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.UserDefinedType Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.Role Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.User Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.Index) Then
.Selected = False
Msg = "exists only in target database and will not be removed from the target database."
End If
' Schema, Role, User will be updated only if it exists in the source schema and not in the target schema.
If Not .Type = RedGate.Shared.ComparisonInterfaces.DifferenceType.OnlyIn1 Then
If (.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.Schema Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.Role Or
.ObjectIn2.ObjectType = RedGate.SQLCompare.Engine.ObjectType.User) Then
.Selected = False
Msg = " exists in target database. Target database will not be updated."
End If
End If
End If
End With
Next

Rob C · September 29, 2015 8:15AM

Hi,

Sorry for the delay in getting back to you. There's no easy way of doing what you're asking, but I've discovered a way of achieving it by tricking the engine into thinking that the trigger is also in the source table. You need to do something like the following (this is C# but that's what I'm most familiar with)

var sourceDatabase = new Database();
var targetDatabase = new Database();

sourceDatabase.Register(new ConnectionProperties("sourceServer", "sourceDatabase"), Options.Default);
targetDatabase.Register(new ConnectionProperties("targetServer", "targetDatabase"), Options.Default);

Differences differences = sourceDatabase.CompareWith(targetDatabase, Options.Default);

foreach (var difference in differences)
{
	if (difference.Type == DifferenceType.Different && difference.DatabaseObjectType == ObjectType.Table)
	{
		var sourceTable = difference.ObjectIn1 as Table;
		var targetTable = difference.ObjectIn2 as Table;

		foreach (var trigger in targetTable.Triggers)
		{
			if (sourceTable.Triggers.Count(x =&gt; x.Name == trigger.Name) == 0)
			{
				sourceTable.Triggers.Add(trigger);
			}
		}
	}
}

I think the VB code would be something like this -

Dim sourceDatabase = New Database()
Dim targetDatabase = New Database()
sourceDatabase.Register(New ConnectionProperties("PS-ROBERTCSQL2014", "doc_breaker"), Options.Default)
targetDatabase.Register(New ConnectionProperties("PS-ROBERTCSQL2014", "doc_breaker2"), Options.Default)

Dim differences As Differences = sourceDatabase.CompareWith(targetDatabase, Options.Default)

For Each difference In differences
	If difference.Type = DifferenceType.Different AndAlso difference.DatabaseObjectType = ObjectType.Table Then
		Dim sourceTable = TryCast(difference.ObjectIn1, Table)
		Dim targetTable = TryCast(difference.ObjectIn2, Table)

		For Each trigger In targetTable.Triggers
			If sourceTable.Triggers.Count(Function(x) x.Name = trigger.Name) = 0 Then
				sourceTable.Triggers.Add(trigger)
			End If
		Next
	End If
Next

Then you can recompare sourceDatabase and targetDatabase to generate a deployment script which will no longer drop the tables. It's not an ideal way of doing things, but it seems to work.

Ignore triggers and indexes

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk