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:
Select the first data provider to connect to Microsoft SQL Server and click Next:
Select the logs.view_query_list in the Query List, uncheck the checkbox as shown, and select logs.view_objects:
In the next screen, select the schema and has_log_triggers field to place to the ribbon to filter data and click OK:
Select cell B3 to insert a table and click OK.
The SaveToDB add-in inserts the table. You see the database objects including columns to display triggers:
Format the table as you like and save the workbook:
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:
You have to set 1 into the execute_script field to confirm creating triggers:
Check the target table and click OK.
The SaveToDB add-in updates the table, and you see actual triggers for the captured tables:
In this example, we have created triggers for the dbo25.members table. Its triggers look like:
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:
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.
You can use the Actions menu to drop all triggers at once and the ribbon parameters to filter objects:
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.
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.