Disabling triggers to support data generation

Jamie Laflen, Tech Lead for database unit testing, has developed a code sample to show you how to automatically disable triggers to support data generation.

 

The scenario is that you have delete or insert triggers defined on your database that inhibit proper test data generation. So this sample code will allow you to modify your database unit test setup such that before generating test data, the triggers will be disabled and then re-enabled after the data has been appropriately generated. This is an important scenario that customers have been running into when attempting to generate data for, say, the AdventureWorks database that has delete triggers defined.

 

To use the sample code, replace your existing DatabaseSetup.cs in your test project with the code found below. You will have to update the namespace, etc. to be appropriate for your test project. The sample code uses SMO to determine what insert and delete triggers exist in your database and then appropriately disables them. Make sure you thus add the appropriate reference to SMO to your test project.

 

//-----------------------------------------------------------------------

// This file is part of:

// Visual Studio Team Edition for Database Professionals

//

// Copyright (C) Microsoft Corporation. All rights reserved.

//

// This source code is intended only as a supplement to Microsoft

// Development Tools and/or on-line documentation. See these other

// materials for detailed information regarding Microsoft code samples.

//

// THIS CODE AND INFORMATION ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY

// KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE

// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

// PARTICULAR PURPOSE.

//-----------------------------------------------------------------------

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Configuration;

using System.Data.SqlClient;

using System.Diagnostics;

using Microsoft.VisualStudio.TestTools.UnitTesting;

using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

namespace TestProject1

{

    [TestClass()]

    public class DatabaseSetup

    {

        [AssemblyInitialize()]

        public static void IntializeAssembly(TestContext ctx)

        {

            // Setup the test database based on setting in the

            // configuration file

            DatabaseTestClass.TestService.DeployDatabaseProject();

            ConnectionContext triggerCtx = DatabaseTestClass.TestService.OpenPrivilegedContext();

            List<Trigger> disabledTriggers =

                DisableTriggersForPopulate(triggerCtx.Connection as SqlConnection);

            try

            {

               DatabaseTestClass.TestService.GenerateData();

            }

            finally

            {

                EnableTriggers(disabledTriggers);

                triggerCtx.Dispose();

            }

        }

        /// <summary>

        /// Disables all Insert and Delete triggers defined and enabled on tables

        /// in the database connected to by the supplied connection string.

        /// <remarks>

        /// This disables all triggers by enumerating all tables (and their triggers)

        /// in the entire database. This can have a negative performance, if performance

        /// becomes an issue, a script that directly disables/enables triggers should be

        /// used instead.

        /// </remarks>

        /// </summary>

        /// <param name="targetConn"></param>

        /// <returns></returns>

        private static List<Trigger> DisableTriggersForPopulate(SqlConnection targetConn)

        {

            if (targetConn == null)

            {

                throw new ArgumentNullException("targetConn");

            }

            ServerConnection serverConn = new ServerConnection(targetConn);

            Server targetServer = new Server(serverConn);

            Database db = targetServer.Databases[targetConn.Database];

            // Loop through all tables and disable all Insert/Delete triggers

            //

            List<Trigger> disabledTriggers = new List<Trigger>();

            foreach (Table currentTable in db.Tables)

            {

                foreach (Trigger currentTrigger in currentTable.Triggers)

                {

                    if (currentTrigger.IsEnabled)

                    {

                        if (currentTrigger.Insert || currentTrigger.Delete)

                        {

                            currentTrigger.IsEnabled = false;

                            currentTrigger.Alter();

                            Debug.Print("Disabled {0}", currentTrigger.Name);

                            disabledTriggers.Add(currentTrigger);

                        }

                    }

                }

            }

            return disabledTriggers;

        }

        /// <summary>

        /// Enables all supplied triggers

        /// </summary>

        /// <param name="triggers"></param>

        private static void EnableTriggers(List<Trigger> triggers)

        {

            if (triggers == null)

            {

                return;

            }

            foreach (Trigger t in triggers)

            {

                t.IsEnabled = true;

                t.Alter();

                Debug.Print("Enabled {0}", t.Name);

            }

        }

    }

}

 

Hope that helps!

 

Jamie Laflen

Sachin Rekhi

Comments

• Anonymous
  January 09, 2007
  Tiago Pascoal on Team Foundation Server: Restricting Work Item creation based on a role. Tim Murphy...