Chapter 2. Change Tracking Framework Setup

Chapter 2. Change Tracking Framework Setup

Creating Administrator's Workbook

You can use Microsoft Excel to manage change tracking framework objects.

Create a new workbook and run Wizards, Data Connection Wizard:

Excel Data Connection Wizard

Select the first data provider to connect to Microsoft SQL Server and click Next:

Data Provider for Microsoft SQL Server

Select the logs.view_query_list in the Query List, uncheck the checkbox as shown, and select logs.view_objects:

Connecting to Change Tracking Framework Objects in Excel

In the next screen, select the schema and has_log_triggers field to place to the ribbon to filter data and click OK:

Excel Data Connection Wizard Parameters

Select cell B3 to insert a table and click OK.

Target Address of Inserted Table in Excel

The SaveToDB add-in inserts the table. You see the database objects including columns to display triggers:

Connected Table in Excel

Format the table as you like and save the workbook:

Excel Table to Manage Change Tracking Objects

Creating Change Tracking Triggers

The framework creates three triggers to log INSERT, UPDATE, and DELETE operations for captured tables.

Use the context menu to create and drop triggers:

Excel Context Menu to Manage Change Tracking Objects

You have to set 1 into the execute_script field to confirm creating triggers:

Operation Confirmation Dialog Box

Check the target table and click OK.

The SaveToDB add-in updates the table, and you see actual triggers for the captured tables:

Created Triggers to Track Changes in Excel

In this example, we have created triggers for the dbo25.members table. Its triggers look like:

Created Triggers to Track Changes in SQL Server

The change tracking framework also creates a stored procedure to select changes.

The procedures have the xl_log_ prefix. You can use filters in SSMS to filter change-tracking objects:

Stored Procedure to Select Track Changes

Note that the framework creates procedures in the schemas of underlying tables.

So, if a user has SELECT and EXECUTE permissions for a schema, he can execute a new procedure.

The framework configures such procedures as SaveToDB context menu items for underlying tables.

Useful Operations

You can use the Actions menu to drop all triggers at once and the ribbon parameters to filter objects:

Actions Menu of Change Tracking Framework in Excel

Attaching Context Menus

The framework configures procedures to show changes as SaveToDB context menu items for underlying tables.

To attach context menu items to other objects, edit the link data in the logs.base_tables table.

You can do this in Excel. Just connect to the table, edit the data and click the Save button.

Attaching Excel Context Menus to Database Objects

In this example, we have attached dbo25.members context menu items to the xls25.usp_members procedure.

As a result, users can track changes using the context menu of the xls25.usp_members procedure.

They need click Reload, Reload Data and Configuration to load new context menu items.

Note that the target object must select all primary key fields of the base table with the same names.