Change Tracking Framework Tables
| Table | Description |
|---|---|
| logs.base_tables | This user table contains a configuration used to attach context menu items to database objects. |
| logs.change_logs | This application table contains change tracking data for all captured tables. |
| logs.formats | This application table contains Excel table formats of the change tracking framework objects. |
| logs.handlers | This application table contains the event handler configuration of the change tracking framework objects. |
| logs.tables | This application table contains initial object_id and table names. |
| logs.translations | This application table contains translation data of the change tracking framework objects. |
| logs.workbooks | This application table contains a workbook configuration for SaveToDB Application Workbooks wizard. |
logs.base_tables
This user table contains a configuration used to attach context menu items to database objects.
| Column | DataType | Null | Comment | |
|---|---|---|---|---|
| ✓ | ID | int | Identity | |
| OBJECT_SCHEMA | nvarchar(128) | Configured object schema | ||
| OBJECT_NAME | nvarchar(128) | Configured object name | ||
| BASE_TABLE_SCHEMA | nvarchar(128) | Underlying table schema | ||
| BASE_TABLE_NAME | nvarchar(128) | Underlying table name |
Use this 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.
Example:
See also Attaching Context Menus.
logs.change_logs
This application table contains change tracking data for all captured tables.
| Column | DataType | Null | Comment | |
|---|---|---|---|---|
| ✓ | change_id | int | Identity Unique change ID | |
| object_id | int | object_id of the table of changes calculated by the OBJECT_ID function | ||
| id | int | ✓ | IDENTITY field value of the table of changes if the field exists | |
| keys | nvarchar(445) | ✓ | The string value of keys of the table of changes | |
| inserted | xml | ✓ | XML value of the inserted row and new values of the updated row | |
| deleted | xml | ✓ | XML value of the deleted row and old values of the updated row | |
| change_type | tinyint | Change type:
1 - INSERT 2 - DELETE 3 - UPDATE | ||
| change_date | datetime | Date and time of changes calculated by the GETDATE function | ||
| change_user | nvarchar(128) | A user of changes calculated by the GETUSER function |
Do not edit this table manually.
The table contains primary keys and both current and previous row values of source records in an XML format.
logs.formats
This application table contains Excel table formats of the change tracking framework objects.
| Column | DataType | Null | Comment | |
|---|---|---|---|---|
| ✓ | ID | int | Identity | |
| TABLE_SCHEMA | nvarchar(128) | xls.formats.TABLE_SCHEMA | ||
| TABLE_NAME | nvarchar(128) | xls.formats.TABLE_NAME | ||
| TABLE_EXCEL_FORMAT_XML | xml | ✓ | xls.formats.TABLE_EXCEL_FORMAT_XML | |
| APP | nvarchar(50) | ✓ |
This table is a copy of the xls.formats table of the SaveToDB Framework to avoid external dependency.
See also Configuring Table Formats.
logs.handlers
This application table contains the event handler configuration of the change tracking framework objects.
| Column | DataType | Null | Comment | |
|---|---|---|---|---|
| ✓ | ID | int | Identity | |
| TABLE_SCHEMA | nvarchar(20) | xls.handlers.TABLE_SCHEMA | ||
| TABLE_NAME | nvarchar(128) | xls.handlers.TABLE_NAME | ||
| COLUMN_NAME | nvarchar(128) | ✓ | xls.handlers.COLUMN_NAME | |
| EVENT_NAME | varchar(25) | xls.handlers.EVENT_NAME | ||
| HANDLER_SCHEMA | nvarchar(20) | ✓ | xls.handlers.HANDLER_SCHEMA | |
| HANDLER_NAME | nvarchar(128) | ✓ | xls.handlers.HANDLER_NAME | |
| HANDLER_TYPE | nvarchar(25) | ✓ | xls.handlers.HANDLER_TYPE | |
| HANDLER_CODE | nvarchar(max) | ✓ | xls.handlers.HANDLER_CODE | |
| TARGET_WORKSHEET | nvarchar(128) | ✓ | xls.handlers.TARGET_WORKSHEET | |
| MENU_ORDER | int | ✓ | xls.handlers.MENU_ORDER | |
| EDIT_PARAMETERS | bit | ✓ | xls.handlers.EDIT_PARAMETERS |
This table is a copy of the xls.handlers table of the SaveToDB Framework to avoid external dependency.
The framework also inserts and deletes handlers of every captured table here.
Such handlers define the context menu actions to select and revert changes.
See also Configuring Event Handlers.
logs.tables
This application table contains initial object_id and table names.
| Column | DataType | Null | Comment | |
|---|---|---|---|---|
| ✓ | object_id | int | Table object_id obtained on creating change tracking triggers. | |
| TABLE_SCHEMA | nvarchar(128) | Table schema obtained on creating change tracking triggers. | ||
| TABLE_NAME | nvarchar(128) | Table name obtained on creating change tracking triggers. |
The application uses this table to find the target table after changes in object_id or table names, but not in both.
logs.translations
This application table contains translation data of the change tracking framework objects.
| Column | DataType | Null | Comment | |
|---|---|---|---|---|
| ✓ | ID | int | Identity | |
| TABLE_SCHEMA | nvarchar(128) | xls.translations.TABLE_SCHEMA | ||
| TABLE_NAME | nvarchar(128) | xls.translations.TABLE_NAME | ||
| COLUMN_NAME | nvarchar(128) | ✓ | xls.translations.COLUMN_NAME | |
| LANGUAGE_NAME | varchar(10) | xls.translations.LANGUAGE_NAME | ||
| TRANSLATED_NAME | nvarchar(128) | ✓ | xls.translations.TRANSLATED_NAME | |
| TRANSLATED_DESC | nvarchar(255) | ✓ | xls.translations.TRANSLATED_DESC | |
| TRANSLATED_COMMENT | nvarchar(4000) | ✓ | xls.translations.TRANSLATED_COMMENT |
This table is a copy of the xls.translations table of the SaveToDB Framework to avoid external dependency.
You may edit the translation and add new translations for the required languages.
You may use the logs.usp_translations procedure to edit translation for multiple languages in a pivot table and the logs.view_translations view to check and edit the translation of all database objects.
See also Configuring Data Translation.
logs.workbooks
This application table contains a workbook configuration for SaveToDB Application Workbooks wizard.
| Column | DataType | Null | Comment | |
|---|---|---|---|---|
| ✓ | ID | int | Identity | |
| NAME | nvarchar(128) | Workbook name | ||
| TEMPLATE | nvarchar(255) | ✓ | Template workbook path or URL.
The template workbook is not necessary. The add-in can create a workbook using the definition only. However, you may supply the workbook to have special features. You may use Wizards, Developer Wizard, Prepare Workbook for Distribution to remove sensitive data and passwords from the workbook. | |
| DEFINITION | nvarchar(max) | Workbook definition.
Use the Help, Workbook Information, Workbook Definition menu item to get the definition of any opened workbook. | ||
| TABLE_SCHEMA | nvarchar(128) | ✓ | Workbook related schema |
The SaveToDB add-in allows generating workbooks using the Application Workbooks wizard.
The add-in loads the workbook configuration from this table.
This is an application table.
If you need to generate other application workbooks, use the xls.workbooks table.
See also Configuring Application Workbooks.