Skip to main content
Mitratech Success Center

Client Support Center

Need help? Click a product group below to select your application and get access to knowledge articles, webinars, training content, and release notes or to contact our support team.

Authorized users - log in to create a ticket, view tickets status and check your success plan details.

 

Auditing the Database

Database level auditing is available for Suite. This type of auditing is performed on the database side, completely independent of the application. All transactions in the database are logged in auditing tables by database triggers. To activate database auditing, scripts provided by Mitratech must be run to create the auditing tables and triggers in the database. Once the tables and triggers have been created, the database will log any insert, update, or delete transactions against the audited tables. All tables that store user-entered data are audited; only some system tables—such as those that store information about most recently accessed items, searches, etc.—are not audited.

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
  • Was this article helpful?