How to use the Lookup feature in ApexSQL Trigger

Applies to
ApexSQL Trigger

Summary
This article explains how to use the Lookup feature in ApexSQL Trigger and demonstrate it through an example

Description

What is a Lookup, and why to use it?

A Lookup is a field from a specific (related) table, added to an auditing report, based on a specified condition, when a value in an audited table is changed

Lookups are used to improve the auditing report readability, in a way to add a meaningful value from a related table when some ID field is changed (updated, inserted, or deleted)

How to create a Lookup?

To illustrate the Lookup feature, in the blank database called Lookup_DB, we will create the following tables:

CREATE TABLE [dbo].[Clients](
	[ClientID] [int] NOT NULL,
	[Name] [varchar](50) NULL,
	[Email] [varchar](50) NULL,
	[CompanyName] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[ClientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Invoices](
	[InvoiceID] [int] NOT NULL,
	[Amount] [int] NULL,
	[Dates] [date] NULL,
	[Client] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[InvoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Invoices]  WITH CHECK ADD FOREIGN KEY([Client])
REFERENCES [dbo].[Clients] ([ClientID])
GO

After importing sample data, tables will look like as follows:

The Client column from the Invoices table is referencing the ClientID column from the Clients table

We will create a Lookup for the Invoices table (for Lookup, it is Base table). Each time a value from the Client column is changed in a way the new value equals a value from the ClientID column in the related Clients table (for Lookup it is Related table), an additional row will be added to an auditing report

Quick tip: An additional row will be added to the auditing report only if the specified values from Base and Related tables are equal

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

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

To add a Lookup for a specific table, it needs to be highlighted in the main grid. Inside the Lookup tab, click the Add button, to open the Add a Lookup for the table dialog:

In the Name text box, add a descriptive text to explain the actual change. The content of the field will be presented in the auditing report under the Column name column

The Related table is table that the base table is compared with. Any table from the existing database can be selected from the drop down list

Quick tip: The Related table drop down list is limited to tables from the existing database, and does not contain tables from the external databases.

In the Condition section, choose columns from the Base and Related tables whose values need to be equal, when the specified operation (INSERT, UPDATE, or DELETE) is executed

Quick tip: Multiple fields from the Base table can be assigned to multiple fields from the Related table.

From the Base drop down list, pick a column from the base table whose value needs to be changed in order for the Lookup to be triggered (in this case the Client column from the Invoices table is selected)

From the Related drop down list, pick a column from the related table whose value needs to be equal comparing to changed (new) value from a “related” column (in this case the ClientID column from the Clients table)

The Reported value is from a column in a related table whose change will be shown in Old value and New value columns in an auditing report

The reported value can be presented as:

• Column – from a related table (pick a column from the drop down list)
• Expression – offers more accurate presentation of the old/new values

The Expression field allows defining values in a specific way, in order to improve readability of the auditing report. For example:

‘Client name: ‘+ {table}.Name + ‘ / ID:’ + LTrim(Str({table}.ClientID))

The above text will generate an auditing report with customized values: