Advantage Developer Zone

 
 
 

Using Triggers to Maintain an Audit Table

Friday, July 14, 2006

In many cases there is a need to see who has made changes to a particular record in a table. This audit information can be a useful trouble-shooting tool. If this audit information is maintained by your application, however, changes made by other third-party tools or applications to the database will not be captured.

Creating a trigger that writes to an audit table on every insert, update, or delete operation insures that the audit trail is accurate. Triggers are maintained at the database level by Advantage and run regardless which application is accessing the data.

Trigger Basics

Triggers are a piece of code that is run on the server in response to an event on a particular table. Triggers are defined inside an Advantage Data Dictionary. Therefore, a connection to the data dictionary is required for trigger execution. The trigger code is implemented inside of a trigger container. A trigger container can be an SQL script, .NET Assembly, Win32 DLL, COM object, or Linux shared object. The data and source code for this tech tip are available for download here.

SQL Audit Trigger

In order to log activity, we will need a table to write the activity information to. For this example, we have created a table called AuditTrail that contains 4 fields: TableName, Action, ModifiedBy, and ModifiedDate. The following SQL statement will insert a record into the AuditTrail table when a record is updated in the Customer table.

INSERT INTO AuditTrail SELECT Customer as TableName, 'Update' as Action, USER() as ModifiedBy, NOW() as ModifiedDate FROM __new

After opening the AuditDemo Dictionary, use the following steps to create a trigger on the Customer table:

  1. Select Triggers under the Customer table
  2. Change the Trigger Type to AFTER
  3. Change the Event Type to UPDATE
  4. Type the following into the description field: Inserts a record into AuditTrail when a record is updated
  5. Type the above SQL statement into the text box under the Script tab
  6. Click Save and type a name for your new trigger.

NOTE: Object names must be unique in the dictionary. You cannot use the same name more than once for a dictionary object. We used Customer_Update as the trigger name in the sample code.

Open the Customer table and modify a record. Then open the AuditTrail table to view the audit information. The following statements can be used for Insert and Delete triggers.

AFTER INSERT

INSERT INTO AuditTrail SELECT 'Customer' as TableName, 'Insert' as Action, USER() as ModifiedBy, NOW() as ModifiedDate FROM __new

AFTER DELETE

INSERT INTO AuditTrail SELECT 'Customer' as TableName, 'Delete' as Action, USER() as ModifiedBy, NOW() as ModifiedDate FROM __new

Logging Additional Information

The trigger example above demonstrates how to log actions performed on tables in a dictionary.  However, they do not indicate specifically what was inserted, updated, or deleted from the table. There are two ways to log this type of information.

The first option is to create an audit table for each table you wish to keep detailed logs about. The table would have to have the same structure as the original table with the addition of three fields to contain the user name of the person making the modification, the action performed, and the date and time of the action. This could again be done using an SQL trigger like the following:

INSERT INTO Customer_Audit SELECT *, 'Update' as Action, USER() as ModifiedBy, NOW() as ModifiedDate FROM __new

Another option is to maintain a single table that contains the log information. In this case, our table is similar to the AuditTrail table described above with the addition of a Memo field called Changes to contain the change information. Our new table is called AuditDetail and a Win32 DLL will modify it. The DLL used for this example was written in Delphi 7 using the Advantage Trigger Template. Use the following steps to use this trigger container with the SalesReps table.

Select Triggers under the SalesReps table

  1. Change the Trigger Type to AFTER
  2. Change the Event Type to UPDATE
  3. Type the following into the description field: Logs changes in the AuditDetail table
  4. Select the Windows DLL or Linux Shared Object tab
  5. Browse to the AuditTrig.dll in the dictionary directory
  6. Type Update_Audit as the Function Name
  7. Click Save and type a name for your new trigger. We used SalesReps_Update as the trigger name.

Now that the trigger is defined, open the SalesReps table and change one or more records. Open the AuditDetail table to see information about your changes. The code in the AuditTrig.dll is generic and will work for an update on any table.

For more information about triggers, see the Advantage Help files or visit the Advantage Developer Zone at DevZone.AdvantageDatabase.com.