Chapter 5. Change Tracking Framework 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.
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 can 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 can 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 can 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 can 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 can 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 can 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.