Change Tracking Framework
for Microsoft SQL Server
Procedures
| Procedure | Description |
|---|---|
| logs.usp_translations | This procedure is an Excel form to select and edit translations in all languages. |
| logs.usp_translations_change | This procedure updates a database on cell changes of logs.usp_translations. |
| logs.xl_actions_clear_logs | This procedure clears change tracking records of the specified target table. |
| logs.xl_actions_create_change_tracking_triggers | This procedure creates change tracking triggers of the specified target table. |
| logs.xl_actions_drop_all_change_tracking_triggers | This procedure drops all change tracking triggers and clears all logs. |
| logs.xl_actions_drop_change_tracking_triggers | This procedure drops change tracking triggers of the specified target table. |
| logs.xl_actions_restore_current_record | This procedure restores the current version of the changelog record. |
| logs.xl_actions_restore_previous_record | This procedure restores the previous version of the changelog record. |
| logs.xl_actions_restore_record | This procedure restores a row from the changelog record. |
| logs.xl_actions_select_lookup_id | This procedure selects rows from tables referenced by foreign keys. |
| logs.xl_actions_select_record | This procedure selects change details of a changelog record. |
| logs.xl_actions_select_records | This procedure selects changelog records. |
| logs.xl_actions_set_role_permissions | This procedure sets permissions for the log_administrators and log_users roles. |
| logs.xl_export_settings | This procedure exports Change Tracking Framework settings. |
| logs.xl_import_handlers | This procedure imports Change Tracking Framework settings into logs.handlers. |
logs.usp_translations
This procedure is an Excel form to select and edit translations in all languages.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @field | nvarchar(128) | IN | The source field of the logs.translations table: TRANSLATED_NAME, TRANSLATED_DESC, or TRANSLATED_COMMENT. |
Editing data requires the SaveToDB Enterprise edition.
logs.usp_translations_change
This procedure updates a database on cell changes of logs.usp_translations.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @column_name | nvarchar(128) | IN | The column name of the edited cell (expected logs.translations.LANGUAGE_NAME). |
| @cell_value | nvarchar(max) | IN | A new value of the edited cell used to update for the source field. |
| @TABLE_SCHEMA | nvarchar(128) | IN | logs.translations.TABLE_SCHEMA |
| @TABLE_NAME | nvarchar(128) | IN | logs.translations.TABLE_NAME |
| @COLUMN | nvarchar(128) | IN | logs.translations.COLUMN_NAME |
| @field | nvarchar(128) | IN | The source field of the xls.translations table: TRANSLATED_NAME, TRANSLATED_DESC, or TRANSLATED_COMMENT. |
logs.xl_actions_clear_logs
This procedure clears change tracking records of the specified target table.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @schema | nvarchar(128) | IN | Target table schema |
| @name | nvarchar(128) | IN | Target table name |
| @confirm | bit | IN | 1 - Clear logs
0 - Cancel |
| @data_language | char(2) | IN | The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter. |
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.
logs.xl_actions_create_change_tracking_triggers
This procedure creates change tracking triggers of the specified target table.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @schema | nvarchar(128) | IN | Target table schema |
| @name | nvarchar(128) | IN | Target table name |
| @execute_script | bit | IN | 1 - Execute scripts to create triggers |
| @data_language | char(2) | IN | The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter. |
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.
logs.xl_actions_drop_all_change_tracking_triggers
This procedure drops all change tracking triggers and clears all logs.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @schema | nvarchar(max) | IN | Target schema to drop all change tracking triggers or NULL to drop in all schemas |
| @execute_script | bit | IN | 1 - Execute scripts to drop triggers |
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.
logs.xl_actions_drop_change_tracking_triggers
This procedure drops change tracking triggers of the specified target table.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @schema | nvarchar(128) | IN | Target table schema |
| @name | nvarchar(128) | IN | Target table name |
| @execute_script | bit | IN | 1 - Execute scripts to drop triggers |
| @data_language | char(2) | IN | The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter. |
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.
logs.xl_actions_restore_current_record
This procedure restores the current version of the changelog record.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @change_id | int | IN | Change record ID |
| @confirm | bit | IN | 1 - Restore the record
0 - Print SQL to restore the record |
See Restoring Records.
The procedure executes the logs.usp_restore_record procedure with the predefined parameter.
logs.xl_actions_restore_previous_record
This procedure restores the previous version of the changelog record.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @change_id | int | IN | Change record ID |
| @confirm | bit | IN | 1 - Restore the record
0 - Print SQL to restore the record |
See Restoring Records.
The procedure executes the logs.usp_restore_record procedure with the predefined parameter.
logs.xl_actions_restore_record
This procedure restores a row from the changelog record.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @change_id | int | IN | Change record ID |
| @restore_previous | bit | IN | 1 - Restore the previous record
0 - Restore the current record |
| @confirm | bit | IN | 1 - Restore the record
0 - Print SQL to restore the record |
| @data_language | char(2) | IN | The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter. |
A user must have the UPDATE permission on the captured table.
logs.xl_actions_select_lookup_id
This procedure selects rows from tables referenced by foreign keys.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @change_id | int | IN | Change record ID |
| @column_name | nvarchar(128) | IN | The column name of the active cell |
| @name | nvarchar(128) | IN | The name column value |
| @data_language | char(2) | IN | The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter. |
See ID Lookup.
A user must have the SELECT permission on the captured and referenced tables.
logs.xl_actions_select_record
This procedure selects change details of a changelog record.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @change_id | int | IN | Change record ID |
| @data_language | char(2) | IN | The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter. |
See Task Panes.
A user must have the SELECT permission on the captured table.
logs.xl_actions_select_records
This procedure selects changelog records.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @schema | nvarchar(128) | IN | Target table schema |
| @name | nvarchar(128) | IN | Target table name |
| @id | int | IN | The value of the identity column if exists |
| @keys | nvarchar(445) | IN | The string value of the key columns |
| @change_type | tinyint | IN | NULL - all changes
1 - INSERT 2 - DELETE 3 - UPDATE |
| @data_language | char(2) | IN | The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter. |
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.
logs.xl_actions_set_role_permissions
This procedure sets permissions for the log_administrators and log_users roles.
See Administrator Permissions and See User Permissions.
logs.xl_export_settings
This procedure exports Change Tracking Framework settings.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @part | tinyint | IN | NULL - all
2 - logs.handlers 3 - logs.translations 4 - logs.formats 5 - logs.workbooks 6 - create triggers 7 - logs.base_tables |
| @sort_by_names | bit | IN | 1 - sort by names
0 - sort by id (default) |
| @schema | nvarchar(128) | IN | Target schema to export settings or NULL to export all settings.
Use 'x' to export help for all schemas except doc, logs, and xls. |
| @language | char(2) | IN | logs.translations.LANGUAGE_NAME |
Use the 'x' value for the schema to export your database settings only, skipping the framework settings.
logs.xl_import_handlers
This procedure imports Change Tracking Framework settings into logs.handlers.
| Parameter | DataType | Mode | Comment |
|---|---|---|---|
| @TABLE_SCHEMA | nvarchar(20) | IN | logs.handlers.TABLE_SCHEMA |
| @TABLE_NAME | nvarchar(128) | IN | logs.handlers.TABLE_NAME |
| @COLUMN_NAME | nvarchar(128) | IN | logs.handlers.COLUMN_NAME |
| @EVENT_NAME | varchar(25) | IN | logs.handlers.EVENT_NAME |
| @HANDLER_SCHEMA | nvarchar(20) | IN | logs.handlers.HANDLER_SCHEMA |
| @HANDLER_NAME | nvarchar(128) | IN | logs.handlers.HANDLER_NAME |
| @HANDLER_TYPE | nvarchar(25) | IN | logs.handlers.HANDLER_TYPE |
| @HANDLER_CODE | nvarchar(max) | IN | logs.handlers.HANDLER_CODE |
| @TARGET_WORKSHEET | nvarchar(128) | IN | logs.handlers.TARGET_WORKSHEET |
| @MENU_ORDER | int | IN | logs.handlers.MENU_ORDER |
| @EDIT_PARAMETERS | bit | IN | logs.handlers.EDIT_PARAMETERS |
The procedure is used in the scripts generated by the logs.xl_export_settings procedure.
The procedure tries to update the record first. If the update fails, it inserts a new record.