Overview

The audit functionality delivered with the Stewardship Tier captures user data input but does not record data manipulated by page events or service pages. The enhancements described in this article utilize table triggers to record table inserts, updates, and deletes that are not captured by the user interface. The triggers use the standard audit functionality’s table structure and sit on top of the basic structure requiring no major changes to the table schema or the creation of other database objects.

This guide uses the following supplementary SQL objects to support the implementation of trigger-based auditing: 

Trigger Deployment Objects

• CreateTriggerForAuditTableAll.sql
• CreateTriggerForAuditTable.sql
• _AuditTablesWorklist.sql

Schema Quality SQL Queries

• Tables without Reserved Columns.sql
• Procs without AddedViaChangedVia.sql
• Archive Table Columns out of Sync.sql
• #AuditColumn out of sync.sql

Example Trigger Code

• trgttMARA_AuditRecordUpd
• trgttMARA_AuditRecordIns
• trgttMARA_AuditRecordDel

NOTE: Sample scripts are attached to this article. 

This article contains the following sections: 

• Best Practices
• Generating Triggers
• MDM Review Role Workflow Email
• Trigger Detailed Information

Best Practices

The triggers can be deployed for all desired tables that are already using the standard structure for auditing. Some requirements that align with best practices for the basic audit structure must be met before the triggers can be deployed.

This section contains:

1. Append Columns

2. Check Columns

3. Master Data Management: Audit Table Registration

4. Master Data Management: Archive Tables

5. Event Business Rules

1.     Append Columns

All audited tables must contain the reserved columns that aid in audit. The columns are AddedOn, AddedBy, AddedVia, ChangedOn, ChangedBy, ChangedVia. Refer to Append Syniti Reserved Columns to Tables in the online help for more information.

2.   Check Columns

Verify that your audited tables and the supporting #Audit and #AuditColumn tables are in sync before adding triggers to the audited tables.

Tables with the #Audit suffix contain the primary key for the audited record and do not need to be modified unless the table primary key has changed. If you have made changes to the audited table’s primary key since the #Audit table has been created, verify that both the primary key of the #Audit table is correct and that the index is correct.

Tables with the #AuditColumn suffix must contain all the columns of the audited table with a matching data type. Any missing columns result in missing audit records and mismatched data types, which results in possible errors when the trigger or the user interface attempts to capture a change.

3.    Master Data Management: Audit Table Registration

This section is relevant if auditing tables for Master Data Management and utilizing the Review Role Audit Details feature. For each category, a list of audited tables is registered automatically and can be accessed via the Category page’s Vertical view, on the Rules and Actions tab, by clicking the Table Registration icon. This table and table column list is updated automatically, however if there is a primary key change then you must make some manual changes to the registration. The simplest way is to delete the table registration and then refresh the page. The On Load event repopulates the table and table columns with the correct primary key indicators.

Make sure that any columns added to the audited table are also represented in this registration so that they are included in the aggregated data.

If your audit data is not ending up in the aggregate data in the Review Role Audit Details page:

1. Verify that the primary keys are in sync between the audited table, the #Audit table and the Audit Table Registration.
2. Check that the Last Audit ID is in sync with the maximum boaAuditID in the #Audit table for the audited table. If this is out of sync, delete the table registration and refresh the page to rebuild the entry and reset the counter. A service page is configured to run at a 1-minute interval that populates the aggregate data and updates the registry with the Last Audit ID.

4.   Master Data Management: Archive Tables

If archive tables are being used, verify that the archive tables that are configured have matching schema to the audited tables and their #Audit and #AuditColumn tables. Archive tables have an rt prefix on the table name and must have the same schema. The views that populate these tables are automatically updated to match the schema during the archive process, but you may also want to manually update these to verify that they are up to date. The naming convention of these views is:

 

webArchiveRequests_Archive_rtTableName_1InsSel

webArchiveRequests_Archive_rtTableName_2InsSel

 

5.   Event Business Rules

To capture the business rule name in the audit record, the stored procedure must be updated so that it includes the stored procedure name in the action that the procedure is executing. For inserts, the AddedVia field carries the stored procedure name, and for updates and deletes the ChangedVia field does the same. These fields will be the boaAuditUserID in the #Audit table and ultimately be the value shown in the Review Role Audit Details as the ChangedBy User. So that you have a complete picture of the insert, update, or delete, include the AddedBy and AddedOn or the ChangedBy and ChangedOn in the stored procedure.

For the ChangedBy and AddedBy field you can include a parameter in the procedure for @boaUserID NVARCHAR(50) that the system automatically populates. Set the appropriate field to this value. For the other fields, the structure of the procedure determines the method you use to set the reserved columns. If the procedure follows standard methodology a view is used to insert or update the data.

For Insert views, include the values to be inserted. The AddedVia column must be the name of the procedure. Since the procedure name may exceed the field character limit, adding a function to limit to the first 50 characters is recommended. The AddedOn column is the current date time, using the GETDATE() function returns this reliably. An example of the view output columns:

 

LEFT('webPageName_TableName_Ins',50) AS AddedVia, GETDATE() AS AddedOn

 

The resulting procedure body should look like the below example:

 

INSERT INTO TableName ( ... , AddedVia, AddedOn, AddedBy)

SELECT ..., AddedVia, AddedOn, @BoaUserID FROM webPageName_TableName_InsSel

 

For Update views, include the fields to be updated as well as the values they should be updated to with the correct alias. The ChangedVia column must be the name of the procedure. Like the insert view, use a function to limit to the first 50 characters to abide by the field length of ChangedVia. The ChangedOn column is the current date time, using the GETDATE() function. An example of the view output columns:

 

ChangedBy, ChangedOn, ChangedVia, LEFT('webPageName_fieldname_Upd',50) AS ChangedViaNew, GETDATE() AS ChangedOnNew

 

The resulting procedure body should look like the below example:

 

UPDATE webPageName_fieldname_UpdSel

SET ...

,ChangedBy = @boaAuditID

,ChangedVia = ChangedViaNew

,ChangedOn = ChangedOnNew

 

Procedures that execute delete statements are rare, but if the application’s business rules include one then we use a two-step process to record the appropriate audit information. Since a deleted record would not otherwise contain information such as who, when, or what executed the delete, an update statement must be issued before the delete statement to set the reserved columns to store this information.

The update statement follows the same methodology outlined above for an Update business rule with the addition of setting the field AddedBy = N'PreDelete' . This informs the Delete trigger that this record is relevant for a Delete Change Type and is not already being recorded by the User Interface. It is critical to have the same criteria for the DELETE statement as the UPDATE Statement so that records are audited accurately.

 

UPDATE TableName

SET           AddedBy = N'PreDelete',

              ChangedOn = ChangedOnNew,

              ChangedBy = @boaUserID,

              ChangedVia = ChangedViaNew

WHERE ...

 

DELETE FROM TableName WHERE ...

 

Generating Triggers

If best practices have been followed, triggers can be deployed without issues. To begin, create the following objects:

Object Type	Object Name	Script Name
Table	_AuditTablesWorklist	_AuditTablesWorklist.sql
Procedure	CreateTriggerForAuditTable	CreateTriggerForAuditTable.sql
Procedure	CreateTriggerForAuditTableAll	CreateTriggerForAuditTableAll.sql

The MC database was used to hold these objects but with minimal alterations any database could be used. Further refinement could be made to allow for a user interface for these objects, rather than storing them in the backend.

Once the objects are created, add all the tables that must have triggers added to them into the _AuditTablesWorklist table, including the database that the table resides in. Once complete you may add triggers one at a time using the CreateTriggerForAuditTable procedure or for all at once for every record in the table using the CreateTriggerForAuditTableAll procedure.

This section contains: 

• Create All Triggers
• Create Trigger for Specific Table
• Drop All Triggers
• Drop Triggers for Specific Table

Create All Triggers

USE MC

GO

--Create triggers

EXEC CreateTriggerForAuditTableAll

 

Create Trigger for Specific Table

USE MC

GO

--Create triggers for one database's table for a trigger type

EXEC CreateTriggerForAuditTable @Database ='dgeMaterial_Atlas', @TriggerTable = 'ttRequest_External', @TriggerType = 'Update'

 

EXEC CreateTriggerForAuditTable @Database ='dgeMaterial_Atlas', @TriggerTable = 'ttRequest_External', @TriggerType = 'Insert'

 

EXEC CreateTriggerForAuditTable @Database ='dgeMaterial_Atlas', @TriggerTable = 'ttRequest_External', @TriggerType = 'Delete'

Drop All Triggers

USE MC

GO

--Drop triggers

EXEC CreateTriggerForAuditTableAll @DropTriggersOnly = 1

 

Drop Trigger for Specific Table

USE MC

GO

--Create triggers for one database's table for a trigger type

EXEC CreateTriggerForAuditTable @Database ='dgeMaterial_Atlas', @TriggerTable = 'ttRequest_External', @TriggerType = 'Update', @DropTriggersOnly = 1

 

EXEC CreateTriggerForAuditTable @Database ='dgeMaterial_Atlas', @TriggerTable = 'ttRequest_External', @TriggerType = 'Insert', @DropTriggersOnly = 1

 

Master Data Management – Review Role Workflow Email

NOTE: This section applies to versions of Stewardship Tier before 7.4.7. It is

not relevant for later versions of the product.

For Roles that have a type of Review, the users assigned to that role that have email notifications enabled receive an email notification when the role is available to the user. This email notification as delivered contains the request’s audit details which, with triggers, can contain hundreds or thousands of records. This renders the email notification much larger than before and is less user friendly as the email may time out, be too large to send, or causes the user to scroll to the bottom to find the workflow link. This audit details table can be eliminated with a core mod to the [webRequestRole_RoleWorkflowMessageIns] procedure in the DGE Database. The webReviewRole_MessageBodyUpd procedure that is called must be commented out.

       --Comment out to remove the change log table in review role email notifications.

       --IF ( @RoleType = 'Review' )

       --BEGIN

       --     EXECUTE webReviewRole_MessageBodyUpd

       --       @RequestID,

       --       @Message,

       --       @NewMessageBody = @MessageBody OUTPUT

 

       --     SET @Message = @MessageBody

              --END

Trigger Detailed Information

This section contains

• Trigger Escapes
• Set Based Triggers
• Update Trigger Methodology
• Insert Trigger Methodology
• Delete Trigger Methodology

Trigger Escapes

When auditing is enabled, the user interface logs updates made to tables where enabled. For our triggers we have opted to skip these records to avoid duplicates.

• We evaluate this first for performance reasons and to avoid duplication of audit records.
• If the ChangedVia or AddedVia is another value, such as a stored procedure name, we use that value for the audit log.
• If it has not been updated, then we default to a generic term PageEventRule.

NOTE: Stewardship Tier 7.5 addresses an issue where records inserted via Excel

Import are not included on the Review Role Audit Details page. The records are still logged in the #Audit and #AuditColumn tables. A workaround is to remove the trigger escape for ExcelImport in the trigger that executes after insert.

For the audit details to be complete, we are required to include a PageID as well. We attempt to gather this PageID from the request’s scenario role task information. Additional attempts are made to return the request and Request Details PageID as they may not be included in the scenario role task information. If we cannot determine the PageID then we do not audit the record and return out of the trigger. This is useful to know if there is a table that is being populated that is not related to the scenario role task.

Set Based Triggers

Triggers do not evaluate an insert, update, or delete record by record but with a set-based operation. Because of this, we must use a temporary table to store the primary keys of the #Audit table so that we can use them in the #AuditColumn table. You will see an output clause in the #audit table insert that has the same keyword INSERTED as the trigger, but this is a separate container.

Update Trigger Methodology

For triggers recording audit records after update, MDM compares the columns’ before and after update value. If they are the same value, then MDM inserts a NULL into the #AuditColumn table, if not then it submits the before value and the after value into the #AuditColumn table. The before value record has a [boaAuditChange] value of 0 and the after value record has a [boaAuditChange] value of 1. This results in 2 records in the #AuditColumn for every #Audit record.

NOTE: If you are record locking {LockedOn and LockedBy} the update trigger must ignore

updates to LockedOn and LockedBy

Insert Trigger Methodology

For triggers recording audit records after insert, MDM inserts all columns from the audited table into the #AuditColumn table with a [boaAuditChange]of 1.

Delete Trigger Methodology

After delete triggers use the same strategy for auditing as after insert triggers, inserting the deleted record into the #AuditColumn table. The distinction is that for the trigger to log an audit record, the AddedBy field must be equal to the value in ‘PreDelete’, otherwise the trigger returns and does not continue. This prevents logging delete actions that occur in the user interface multiple times.