Tech Tip:
Lookups are part of the auditing triggers. To create a lookup you must create the trigger, for the table as well.
Lookups are a powerful feature of ApexSQL Audit that allows you to show the Literal, from a related table, along with the Value of a field that was audited. This way a more human readable value can be seen in the audit reports, for example "Ajax Health Store" (the store name) versus 5568 (the store id).
Lookups are associated with the audited field so they will be triggered any time a row is inserted or deleted. They will be triggered if the specific column is changed in an update.
Using lookups, you can define an ad hoc relationship between columns from different tables. As with watch columns, if no audited columns are affected by the data change, then none of the lookup values are captured either.
Lookups provide Human Readable Audit data for reporting purposes. For example, there may be a numeric ID field in a table that was changed but would be meaningless to someone viewing the audit report. By adding a Lookup though, the literal, from the related code table, can be shown in addition to the ID field.
Lookups are added at the special Lookups panel. The Lookups panel can be accessed from the View Menu and it is in the same panel group as Field Grid, Row Key, and Watches. To access it from there, just select the Panel Tab for "Lookups".
|
Tech Tip:
Lookups are part of the auditing triggers. To create a lookup you must create the trigger, for the table as well.
|
The panel will show the currently created lookups for the selected table in the Grid. Note that the Lookup Panel will show only the lookups that are in your project file. For example, if you created a new Project for a database with the already created lookups, the Panel won't show anything.
1. To create a lookup, make sure that the fields you selected are for audited tables:
On the Lookups panel click the 
button.
2. Then enter a Name for your lookup, select a Related Table, a Condition and a Reported Value.
|
See Also:
For more information on the fields in this dialog, click here.
|
In this example, the Sales table contains a numeric column to hold the value for Store_ID. Unfortunately, this is difficult, for a person looking at the audit report, to know the specific store that the sale was associated with just by seeing the ID fields.
So we add a lookup to the Sales table, joining on the common Store_ID column, creating the condition between stor_id field from Base Table: sales.
and stor_id field from Related Table: stores.
|
Tech Tip:
You can add more than one condition pair. That will require if you have several fields in your table keys.
|
So, each time when one of the values from condition will be modified, the changes will be observed in the report. For example:
Our tables use only one condition pair for the relationship, therefore we want to report stor_name field on each stor_id change:
Changing stor_id value in the database, you can now see stor_name filed for each stor_id changes:
As Reported Value you can use an expression, instead of a column.
In this example, we want to report stor_name and city fields for each stor_id changes:
You can now see stor_name and city values for each stor_id changes:
You can test the created lookup using the 
button.
A message box will prompt telling if there are errors or not.
|
Tech Tip:
Adding a Lookup will add a column to the Field Name picklist for the selected Table. You can use this to drill down to just the lookup values.
|
1. Clicking the 
button will display the dialog below:
2. A lookup can also be deleted by selecting it and clicking the 
button.
|
Tech Tip:
Every time you make any change(s) to Lookups or Watches you must re-create the triggers. If not, your changes on Watches / Lookups will not be audited.
|
|
Field |
Description |
|
Name |
Enter the name for the lookup. |
|
|
|
|
Related Table |
Choose a table to relate the base table to. |
|
|
|
|
Condition |
The two tables can be related by choosing from the picklist of every table the field you want as condition. |
|
|
|
|
Reported Value |
From the picklist select the column you want to be the reported value or enter a expression in the text box. The expression must return a string (nvchar datatype) value. |
|
|
|
Copyright © 2008 ApexSQL LLC. All Rights Reserved.
