Ignore triggers and indexes

hcripps

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

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 => 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.