Chapter 5. Change Tracking Framework Database Objects

Roles

The change tracking framework creates the following roles:

• log_administrators
• log_users

log_administrators

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

See actual database permissions in the logs.usp_set_role_permissions_administrators procedure.

log_users

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

See actual database permissions in the logs.usp_set_role_permissions_users procedure.

Schemas

logs

The change tracking framework creates its objects in the logs schema.

Tables

The change tracking framework contains the following tables in the logs schema:

• base_tables
• change_logs
• column_translations
• event_handlers
• object_translations

base_tables

This user table contains a configuration used to attach context menu items to database objects.

See Attaching Context Menus.

change_logs

This application table contains change tracking data for all captured tables. Do not edit it.

The table contains primary keys and both current and previous row values of source records in an XML format.

column_translations

This application table contains column translation data. You may change and add translations.

event_handlers

This application table contains event handler configuration.

The framework inserts and deletes handlers of every captured table here.

Do not edit it.

object_translations

This application table contains object translation data. You may change and add translations.

Views

The change tracking framework contains the following views in the logs schema:

• view_column_translations
• view_event_handlers
• view_object_translations
• view_objects
• view_query_list

view_column_translations

This view selects column translation configuration for the SaveToDB add-in.

view_event_handlers

This view selects event handler configuration for the SaveToDB add-in.

view_object_translations

This view selects object translation configuration for the SaveToDB add-in.

view_objects

This view selects database objects and related change tracking framework information.

Use it to configure change tracking triggers. See Setup.

view_query_list

This view selects change tracking framework objects to connect with Microsoft Excel.

See Creating Administrator's Workbook.

Procedures

The change tracking framework contains the following views in the logs schema:

• usp_clear_logs
• usp_create_triggers
• usp_drop_all_triggers
• usp_drop_triggers
• usp_restore_current_record
• usp_restore_previous_record
• usp_restore_record
• usp_select_lookup_id
• usp_select_record
• usp_select_records
• usp_set_role_permissions_administrators
• usp_set_role_permissions_users

usp_clear_logs

This procedure clears change tracking records of the specified target table.

A user must also have the UPDATE permission on the target table.

You may run it from the context menu of the logs.view_objects view.

See Creating Change Tracking Triggers.

usp_create_triggers

This procedure creates change tracking triggers of the specified target table.

A user must also have the ALTER permission on the target table.

You may run it from the context menu of the logs.view_objects view.

See Creating Change Tracking Triggers and Triggers.

usp_drop_all_triggers

This procedure drops all change tracking triggers and clears all logs.

A user must also have the ALTER permission on altered tables.

You may run it from the Actions menu of the logs.view_objects view.

See Useful Operations.

usp_drop_triggers

This procedure drops change tracking triggers of the specified target table.

A user must also have the ALTER permission on the target table.

You may run it from the context menu of the logs.view_objects view.

See Creating Change Tracking Triggers.

usp_restore_current_record

This procedure restores the current version of the change log record.

See Restoring Records.

The procedure executes the usp_restore_record procedure with the predefined parameter.

usp_restore_previous_record

This procedure restores the previous version of the change log record.

See Restoring Records.

The procedure executes the usp_restore_record procedure with the predefined parameter.

usp_restore_record

This procedure restores a row from the change log record.

A user must have the UPDATE permission on the captured table.

usp_select_lookup_id

This procedure selects rows from tables referenced by foreign keys.

See ID Lookup.

A user must have the SELECT permission on the captured and referenced tables.

usp_select_record

This procedure selects change details of a change log record.

See Task Panes.

A user must have the SELECT permission on the captured table.

usp_select_records

This procedure selects change log records.

Generated procedures like dbo25.xl_log_members execute this procedure to select changes.

See Creating Change Tracking Triggers and Task Panes.

A user must have the SELECT permission on the captured table.

usp_set_role_permissions_administrators

This procedure sets permissions for the log_administrators role. See Administrator Permissions.

usp_set_role_permissions_users

This procedure sets permissions for the log_users role. See User Permissions.

Triggers

The logs.usp_create_triggers procedure generates three triggers for every captured table like these:

• trigger_members_log_insert
• trigger_members_log_update
• trigger_members_log_delete

Below are the trigger examples.

Insert Trigger

CREATE TRIGGER [dbo25].[trigger_members_log_insert]
    ON [dbo25].[members]
    AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON
 
INSERT INTO logs.change_logs    (object_id, id, inserted, deleted, change_type, change_date, change_user)
SELECT
    369488445
    , inserted.[id]
    , (SELECT * FROM inserted FOR XML RAW)
    , NULL
    , 1
    , GETDATE()
    , USER_NAME()
FROM
    inserted
 END

Update Trigger

CREATE TRIGGER [dbo25].[trigger_members_log_update]
    ON [dbo25].[members]
    AFTER UPDATE
AS 
BEGIN
SET NOCOUNT ON
 
INSERT INTO logs.change_logs    (object_id, id, inserted, deleted, change_type, change_date, change_user)
SELECT
    369488445
    , inserted.[id]
    , (SELECT * FROM inserted FOR XML RAW)
    , (SELECT * FROM deleted FOR XML RAW)
    , 3
    , GETDATE()
    , USER_NAME()
FROM
    inserted
 END

Delete Trigger

CREATE TRIGGER [dbo25].[trigger_members_log_delete]
    ON [dbo25].[members]
    AFTER DELETE
AS 
BEGIN
SET NOCOUNT ON
 
INSERT INTO logs.change_logs    (object_id, id, inserted, deleted, change_type, change_date, change_user)
SELECT
    369488445
    , deleted.[id]
    , NULL
    , (SELECT * FROM deleted FOR XML RAW)
    , 2
    , GETDATE()
    , USER_NAME()
FROM
    deleted
 
END

Change Log Table

Triggers save records to the logs.change_logs table that has the following declaration:

CREATE TABLE [logs].[change_logs] (
      [change_id] int IDENTITY(1,1) NOT NULL
    , [object_id] int NOT NULL
    , [id] int NULL
    , [keys] nvarchar(445) NULL
    , [inserted] xml NULL
    , [deleted] xml NULL
    , [change_type] tinyint NOT NULL
    , [change_date] datetime NOT NULL
    , [change_user] nvarchar(128) NOT NULL
    , CONSTRAINT [PK_change_logs_logs] PRIMARY KEY ([change_id])
)

Comments

All triggers of all tables add new change tracking records to the single logs.change_logs table.

If a table has an identity field, the triggers use this field. This is the fastest case to track and select changes.

Otherwise, the triggers pack the key field values into XML. The maximum length is 445 characters.

You cannot use the framework for tables with larger key lengths.

Triggers pack inserted and deleted values into XML.

Also, triggers save the time and user of the changes.