How to customize an auditing report

Applies to
ApexSQL Trigger

Summary
This article explains how to customize an auditing report in a way to preview the exact field from a related table when a field from an audited table is changed

Description
For the purposes of the article, we have created an empty database, and two tables called Client, and Account and inserted sample data as follows:

Client

Account

The goal is to customize an auditing report in a way to create an additional column and to show a specified value from a related table.

Expected scenario:

1. Change the specific value in one table (in this case, value “John Smith” to “John Douglas” from the Client table above)
2. In a report the old and the new value should be presented, as well as the ID value
3. Since the same ID exists in another table, the specified column needs to be shown
4. The following steps explain how to add additional column (in this case Balance column from the Account table) in the report and show its value, as the ID value does not mean to much for the user

Modify the auditing architecture

1. Create an empty database and tables: Client and Account connected via the ClientId column
2. Copy the existing architecture from the following location:
   C:\Users\<user>\AppData\Local\ApexSQL\ApexSQLTrigger2015 and paste it on Desktop for example. Change its name to ApexSQL2053_new.audx
   • Open the newly created architecture in the text editor of your choice and search for KEY4 nvarchar(500)
   • Add the KEY5 nvarchar(500)after KEY4 nvarchar(500) (it comes up twice as a search result). In both cases add KEY5 nvarchar(500)as shown below:

PRIMARY_KEY as PRIMARY_KEY_DATA,  -- for backward compatibility only, don't use this field!  
	DATA_TYPE char(1) NOT NULL DEFAULT 'A',
	KEY1 nvarchar(500),
	KEY2 nvarchar(500),
	KEY3 nvarchar(500),
	KEY4 nvarchar(500),
	KEY5 nvarchar(500)
)

To illustrate it, when you open the architecture in the text editor, initiate searching by CTRL+F, and type the KEY4 nvarchar(500) value in the search field (1). When the first result appears, add a comma after KEY4 nvarchar(500) and add KEY5 nvarchar(500) in a new line (2):

In the Search dialog, click the Find next button, to navigate to another part when the modification needs to be applied. Repeat the previous step:

Quick tip: The change needs to be applied on both places inside the architecture in order to customize it properly

• After this is done, search for KEY4 as, and add the KEY5 as ”Amount” after the KEY4 as ”Key 4”

KEY1 as ''Key 1'',
           KEY2 as ''Key 2'',
           KEY3 as ''Key 3'',
           KEY4 as ''Key 4'',
           KEY5 as ''Amount'',
           d.COL_NAME ''Column Name'',

Quick tip: The change needs to be applied on both places inside the architecture in order to customize it properly

In this way, a column for which you want to show values for, will be created in the report, and the column comes up right after the Key 4 column in the report.

Quick tip: The Amount is the name of the column we want to show in the report. If you need to show another column, specify its name instead of Amount.

Install the architecture

• Start ApexSQL Trigger
• Open the existing project, or create a new one

• From the Advanced tab, click the Manage button:

  

• In the Manage architecture window, click the Open button and navigate to the ApexSQL2053_new.audx architecture file, previously modified:

  

• Click the Install button to install the modified architecture

Quick tip: Installing the modified architecture overrides the currently installed architecture.

Modify and create auditing triggers

• Select tables and fields (columns) to create auditing triggers for

• Click the Create button:

  

Quick tip: It is important not to execute the script for creating triggers, as there are some changes that need to be made in the Script window

• In this particular case we will update the value from the Name column. This means we need to modify the UPDATE trigger script for the table where the Name column belongs, and that is the Client table
• Specifically, in the UPDATE trigger script, we need a part containing the If UPDATE([Name])value
• When the application generates the script to create triggers, it appears in the Script window.
• Initiate search inside the Script window, and specify the If UPDATE([Name]) value

• Add KEY5, and (SELECT TOP 1 Balance FROM dbo.Account a WHERE a.ClientId = OLD.ClientId), as shown below:

  

If UPDATE([Name])
	BEGIN
    
		INSERT
		INTO [TriggerExample].dbo.AUDIT_LOG_DATA
		(
			AUDIT_LOG_TRANSACTION_ID,
			PRIMARY_KEY_DATA,
KEY5,
			COL_NAME,
			OLD_VALUE_LONG,
			NEW_VALUE_LONG,
			DATA_TYPE
			, KEY1
		)
		SELECT
			@AUDIT_LOG_TRANSACTION_ID,
		    convert(nvarchar(1500), IsNull('[ClientId]='+CONVERT(nvarchar(4000), IsNull(OLD.[ClientId], NEW.[ClientId]), 0), '[ClientId] Is Null')),
		    (SELECT TOP 1 Balance FROM dbo.Account a WHERE a.ClientId = OLD.ClientId),
		    'Name',
			CONVERT(nvarchar(4000), OLD.[Name], 0),
			CONVERT(nvarchar(4000), NEW.[Name], 0),
			'A'
			, IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[ClientId], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[ClientId], 0)))

• After making changes, click the Execute button:

  

• Once this is done, update the value in the Client table, from “John Smith” to “John Douglas”

• From the ApexSQL Trigger Home tab, click the Standard report button:

  

• The old and the new values appear, but also, a column Amount we specified in the architecture:

  

Quick tip: The specified steps represent modification that affects only the UPDATE statement for the specified column. To apply the same for other operation (INSERT or DELETE), a corresponding changes must be applied in both, the architecture and the triggers creation script.