How to use the Watches feature in ApexSQL Trigger (4216330)

Description

What is a Watch, and why to use it?

A Watch is a field that will be added to the auditing report if another (audited) field from the same table is changed (inserted, updated, or deleted), even if the added field itself is not changed.

Watches allow adding readily recognizable fields to any audit change, which improves readability of the auditing report.

How to create a Watch?

The Watches panel can be accessed from the View tab, in the Show panels group:

Quick tip: If other panels are already selected, the Watches panel will be added as a tab

As an example, we will use the T1 table containing the following columns: ClientID, FirstName, LastName, and Address.

1. Before creating a watch for the specific table, it needs to be highlighted in the main grid and set as Prepared for auditing, which means it needs to have at least one field, and one operation (INSERT, UPDATE, DELETE) set for auditing. Otherwise, the pick list for the Audit field name will be blank:

   

2. In the Watches panel pick a column from the Audit field name dropdown list that will be audited (for instance, the ClientID column will be selected):

   

Quick tip: If the specific column is not included in auditing under the Columns tab, it won’t appear in the Audit field name list, as it contains only previously selected fields for auditing in the Columns tab

3. From the Watch field name dropdown list, pick a column that will be added to the auditing report when the audited field is changed (for instance, the FirstName column will be selected):

   

Quick tip: The Watch field name list contains all columns for the specific table, even if they are not selected for auditing in the Columns tab

4. Once this is set, click the Add button, to add the watch in the watches list.

Quick tip: Each time a new watch is created, or an existing one is edited, the auditing triggers need to be re-created in order to apply changes to the auditing process

To illustrate this, let’s change ClientID value by executing the statement:

UPDATE [dbo].[T1]
   SET [ClientID] = 3
      
 WHERE [T1].ClientID = 1

When the above query is executed, the auditing report shows the changed value of the ClientID column, and based on the watch definition, it will add the value for the FirstName column in the report, right after the ColumnID value change:

The marked row from the image above is the additional row, created based on the Watch condition.

Quick tip: Multiple watch fields can be added for one audited field, and a single watch field can be assigned to multiple audited fields.

To edit the newly created watch, pick another column from any of the dropdown lists under the Watch tab, and it will be saved automatically. Re-create the triggers in order for it to be included in the auditing process.