Extending Applications
   
 
Audit Trail Implementation
Functionality
The audit mechanism envisaged will show all auditable columns on edit pages as plain text instead of in text boxes. Next to the plain text will be a link or an icon which, when clicked on, brings up another window with the auditable column’s name, a text box next to it with the column’s current data and, in some cases, an empty but mandatory remarks area. The user can enter the appropriate data and click OK, when the pop-up page disappears and the new data appears in the appropriate location – the original page is not refreshed.
Database Design
The table structure necessary for the audit mechanism in ADePT is as shown below.
AuditMaster contains a list of columns that audit needs to be tracked for. The TableDotColumnName has a UNIQUE constraint on it. The RemarksRequired column states whether, when this particular column is edited, remarks are required from the user about, say, the reasons for change. The values for this column are Y (remarks are mandatory), N (remarks are not required and so must not be included), M (remarks are not mandatory, but can be shown).
AuditMaster
Id
TableDotColumnName

RemarksRequired
 Number 
Varchar2 (64)
Char (1)
 

AuditTransaction contains the past values of each of those columns, with information about the users who changed the data and the date and time when that data was changed. The AuditMasterId in the AuditTransaction table is a foreign key to the AuditMaster.

Strictly speaking, the AuditMaster is not required for the audit mechanism to work. It is really just a look-up table for future ADePT designers.

AuditTransaction
Id Number
AuditMasterId Number
UserModifyingData Number
DateTimeDataModified DateTime
PastValue Varchar2 (2000)
Remarks Varchar2 (250)
Mechanism
The mechanism envisaged is not expected to generate audit pages automatically for all the columns that appear in the AuditMaster table. In each case where an audit is required (in the case of each column that needs to be audited, typically), the designer needs to create a UDM that allows the editing of that one column, along with an insert into the AuditTransaction with the past value and the remark.
The steps to implement the audit mechanism in, say, ADePT are as below.
1.
Create the tables above in the database.
2.
Populate the AuditMaster table with the appropriate data, with the columns that need to be audited. The TableDotColumnName must be populated in the format <table_name.column_name>.
3.
For each column that needs audit, create an “auditing UDM” as described below:
a) A passed parameter that carries the name of the column to be edited (and audited) and another parameter that carries the ID of the record to be edited.
b) An edit node that uses the record ID passed to read and update only the column to be edited (apart from reading the key value).
c) A sibling node that takes the passed parameter as a binding and gets the AuditMasterID for that column.
   
  (i)A child node that takes the AuditMasterID and inserts a record with the past data value, user ID, remarks (where necessary), etc.
4.
Change the HTML in the calling edit page to do the following:
 
a)
 Show the auditable column as plan text.
b)
 Put a link next to it that invokes the auditing UDM (in step 3) above with the appropriate parameters, showing the called UDM in a separate window (the window can be modal for IE).
c)
 Add JavaScript that replaces the data in the plain text field with data returned from the UDM in step 3 above.
Notes
   
1.
 The parameters passed to the auditing UDM must be formatted properly, to include the name of the table and column in the <table_name.column_name> format.
2.
 Ensure that the pop-up window includes a Cancel button, so the user can choose to not change the data that shows up.
3.
 Ensure that past data shows up in the text box on the pop-up window.
4.
 The Remarks that are required need to be shown or hidden in the UDM, per the entry in the AuditMaster table. This can be done manually, by the designer.
5.
 In cases where multiple columns are related and need to be audited together (the “project estimate” and “estimated by”, for example), build a UDM (as in step 3) that includes both the columns (3.b) and two insert nodes (3.c).