Contents Diagrams Roles Schemas Tables Views Procedures Functions

Change Tracking Framework
for Microsoft SQL Server

Version 2.0, September 10, 2018

Contents

Introduction

Change Traking Framework

Change Tracking Framework allows tracking and reverting data changes in Microsoft SQL Server databases.

Key principles of the framework:

- Adding tracking features without modifying the underlying tables;
- Managing tracking features in Microsoft Excel;
- Checking and reverting data changes in Microsoft Excel;
- Working in free Microsoft SQL Server Express.

The core concept is simple:

- Adding triggers on INSERT, UPDATE, and DELETE operations to captured tables;
- Storing changes in XML format in the single logs.change_logs table;
- Creating a procedure to select changes for every captured table;
- Generating the required configuration for the SaveToDB add-in to implement the required features.

Here is an example of the context menu for administrators:

Here is an example of the context menu for end-users:

You may read more in the e-book Change Tracking Framework for SQL Server.

This is a technical paper for developers.

You may install, update, and remove Change Tracking Framework using SaveToDB Application Installer wizard.

You may generate a workbook to edit configure tracking using the SaveToDB Application Workbooks wizard.

Feel free to contact us.

Change History

Version 2.0, September 10, 2018

Change Tracking Framework 2.0 requires SaveToDB 8.11 or higher.

New Features:

Impovements:

Bug Fixes:

Diagrams

DiagramDescription
Diagram C01Change Tracking Framework Data Tables
Diagram C02Change Tracking Framework Configuration Tables

Diagram C01. Change Tracking Framework Data Tables

Change Tracking Framework allows tracking changes of the configured tables.

Use the logs.view_captured_objects view to check and create table triggers for tracking changes.

These triggers write table record changes into the logs.change_logs table.

The framework generates the required configuration using logs.view_administrator_handlers and logs.view_translations views to add the context menu items for tracking changes in Microsoft Excel.

Use the logs.base_tables table to link views and procedures to the underlying tables.
The framework generates context menu items for such views and procedures using settings of the specified base tables.

Add users who can check changes to the log_users role.
These users can revert changes if they have INSERT, UPDATE, and DELETE permissions on the underlying tables.

Add users who can configure change tracking features to the log_administrators role.
The administators must have the permission to CREATE and DROP triggers in the target schema.
To clear logs, administators must also have UPDATE permissions on the captured tables.

The generated triggers and procedures use the logs.tables table to find the underlying objects if the underlying object_id or name are changed.

Diagram C02. Change Tracking Framework Configuration Tables

Change Tracking Framework includes complete copies of SaveToDB Framework tables.

This allows using Change Tracking Framework as is, without additional requirements.

The logs.handlers table contains the handler configuration.
It configures action menu items.

The logs.translations table contains translations of database objects, columns, parameters, and generated help headers.

The logs.formats table contains Excel table formats for framework objects.
The SaveToDB add-in applies these formats in the first connection to database objects.
Users can save and reload table formats using SaveToDB Table Format Wizard or Save Table Format and Load Table Format menu items.

The logs.workbooks table contains a configuration to generate a workbook to edit the database help.
Use the Application Workbooks wizard to generate it.

Roles

RoleDescription
log_administratorsThe role includes permissions to use customize tracking features.
log_usersThe role includes permissions to use change tracking features.

log_administrators

The role includes permissions to use customize tracking features.

Assign this role to users who can create and drop triggers and clear change tracking logs.

See actual database permissions in the logs.xl_actions_set_role_permissions procedure.

log_users

The role includes permissions to use change tracking features.

Assign this role to business users who use the change tracking functions.

See actual database permissions in the logs.xl_actions_set_role_permissions procedure.

Schemas

SchemaDescription
logsThe schema contains Change Tracking Framework tables, views, and procedures.

logs

The schema contains Change Tracking Framework tables, views, and procedures.

The schema contains objects used by the application, users, and administrators.

See actual permissions in the logs.xl_actions_set_role_permissions procedure.

Contents

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.