Ignore triggers when gathering dependencies with SMO

Question

I have a system that transfers data between two databases with identical schemas (for development purposes).

The SMO dependency walker works great except for triggers. If I am getting a list of dependencies, I don't care if a trigger introduces a dependency on another table because the trigger will not be executing during the transfer. It's especially annoying if there are circular dependencies.

What I'd like to know is if there's a way to ignore triggers in the dependency walking step. I've tried a few things, with no luck:

• Not including triggers in the list of objects when calling DiscoverDependencies. I'm only including tables anyway.
• Using FilterCallbackFunction on DependencyWalker. This seems to do absolutely nothing.

This is the code I have now:

var tablesToLoad = scripter.WalkDependencies(new DependencyWalker(server).DiscoverDependencies(filteredTables, DependencyType.Parents))
                           .Where(n => n.Urn.Type != "UnresolvedEntity")
                           .Select(n => server.GetSmoObject(n.Urn))
                           .OfType<Table>()
                           .ToArray();

The variable filteredTables contains a list of tables that I want to use for transferring data. The result is all the tables, in their dependency order, that need to be transferred, with dependencies between tables included (including other tables that need to be pulled in to keep foreign key relationships from failing).

EDIT: I want to reiterate that the problem isn't whether triggers are executing or not. The problem is that SMO looks into triggers when determining dependency order for loading tables. So if a trigger references another table, regardless of whether the statements that reference that table would actually ever execute in the trigger, that reference is considered for dependency resolution. I don't want triggers to be used for dependency resolution at all. How do I get SMO to ignore triggers without having to drop them from the database? That's my question.

Answer 1

It looks like the only way to achieve this would be to remove the triggers and then re-add them after the dependency walker completes.

First, get the list of triggers and drop them --

var triggers = database.Triggers;
foreach (var trigger in triggers)
{
    trigger.Drop();
}

Build the dependency tree --

var tablesToLoad = scripter.WalkDependencies(new DependencyWalker(server).DiscoverDependencies(filteredTables, DependencyType.Parents))
                           .Where(n => n.Urn.Type != "UnresolvedEntity")
                           .Select(n => server.GetSmoObject(n.Urn))
                           .OfType<Table>()
                           .ToArray();

Recreate the triggers --

foreach (var trigger in triggers)
{
    trigger.Create();
}