Audited Tables

The following tables support auditing:

• CurrentID
• Document_ID
• Lobby_Audit

AuditTransaction Table

The auditing scripts create a table called AuditTransaction, which tracks all transactions against the audited tables. The AuditTransaction table contains the following fields:

• AuditTransaction_ID – Unique identifier for the transaction.
• TransactionType – Type of transaction (I = insert, U = update, D = delete).
• TransactionUser – Login name of the user from the application or “Unknown” if from a source other than a Suite application.
• TransactionDate – Date of the transaction.
• TableName – Name of the table involved in the transaction.

Triggers

The auditing scripts also create triggers and auditing tables for each audited table. The auditing tables are in the format <TableName>_Audit and contain the following fields:

• old_<FieldName> – The previous value of the updated or deleted field.
• new_<FieldName> – The new value of the updated or inserted field.
• Transaction_ID – Unique identifier for the transaction from the AuditTransaction table.
• TransactionType – Type of transaction (I = insert, U = update, D = delete).
• TransactionUser – Login name of the user from the application or “Unknown” if from a source other than a Suite application.
• TransactionDate – Date of the transaction.

Example

First, a user creates a new address record, which causes the following to happen in the database:

1. A new record is created in the Address table.
2. A new record is created in the AuditTransaction table with the following values:
   AuditTransaction_ID: 201
TransactionType: I
TransactionUser: Jim
TransactionDate: 2004-10-20 14:57:04.483
TableName: ADDRESS
    
3. A new record is created in the Address_Audit table with the following values:
   old_Address_ID: NULL
new_Address_ID: 10035
old_Description: NULL
new_Description: Jim’s Home Address old_Address1: NULL
new_Address1: 1234 Main Street
...
TransactionType: I TransactionUser: Jim
TransactionDate: 2004-10-20 14:57:04.483

Then, the user updates the address record, changing “1234 Main Street” to “101 Second Street”, which causes the following to happen in the database:

1. The record in the Address table is updated.
2. A new record is created in the AuditTransaction table with the following values:
   AuditTransaction_ID: 202
TransactionType: U
TransactionUser: Jim
TransactionDate: 2004-10-20 14:59:05.172
TableName: ADDRESS
   
    
3. A new record is created in the Address_Audit table with the following values:
   old_Address_ID: 10035
new_Address_ID: 10035
old_Description: Jim’s Home Address
new_Description: Jim’s Home Address
old_Address1: 1234 Main Street new_Address1: 101 Second Street
...
TransactionType: U
TransactionUser: Jim
TransactionDate: 2004-10-20 14:59:05.172

Finally, the user deletes the address record, which causes the following to happen in the database:

1. The record in the Address table is deleted.
2. A new record is created in the AuditTransaction table with the following values:
   AuditTransaction_ID: 203
TransactionType: D
TransactionUser: Jim
TransactionDate: 2004-10-20 15:01:01.134
TableName: ADDRESS
3. A new record is created in the Address_Audit table with the following values:
   old_Address_ID: 10035
new_Address_ID: NULL
old_Description: Jim’s Home Address
new_Description: NULL
old_Address1: 1234 Main Street
new_Address1: NULL
...
TransactionType: D TransactionUser: Jim