Event tables

An event table stores information about changes to application tables.

The event table is a database table created by the user, generally within the same schema as the application table for which it stores events. The event table describes the type of change made to an application table, and also contains an identifier for the changed row.

To populate an event table, one or more triggers must be created. A trigger is a database construct that can run an SQL script when a predefined action occurs. For example, a trigger can insert a row in the event table when an update in the application table occurs.

The following table shows some typical columns in an event table, and the reasons for including them.

Column name Column function Example value
EVENT_ID Required. The primary key, which identifies the event being processed. 1
OBJECT_KEY Required. The identifying element of the changed row in the application table, typically the element of the row in the primary key column. cust1
OBJECT_VERB Optional. The change performed, typically one of CREATE, UPDATE, or DELETE. This event is used to distinguish a DELETE event, where the application table contains no row to retrieve when the message for the flow is built. CREATE
OBJECT_NAME Optional. The name of the application table that has changed. This column is required if the DatabaseInput node is being used to support updates to more than one application table. customer
EVENT_PRIORITY Optional. The priority of the event. For example, you can ensure that high value transactions are computed first. 1
EVENT_TIME Optional. The time at which the operation was performed. Generally used for logging or performance monitoring of the flow. 2010-10-19T17:10:00
EVENT_STATUS Optional. Used to determine if the event has already been processed. Required if the events are not to be deleted or archived after processing. 0
EVENT_COMMENT Optional. Free-form field, for example, it can be used to store the outcome of the message processing if the event was not deleted after processing. Processed with exceptions

The column names are examples only. You can use other names. If you have a high-throughput application table, a single row might be changed multiple times between retrieving events from the event table. In this case, only the details of the latest change are picked up by the flow. If a record of intermediate changes is required, include more details in the event table. Also ensure that your event table has enough information about events generated by DELETE operations. Here, because the row in the application table no longer exists, all information required to successfully process the event must be present in the event table.

For example, if a new customer with primary key cust1 is created in the application table, a row is added to the event table:

EVENT_ID OBJECT_KEY OBJECT_VERB
1 cust1 Create

The DatabaseInput node responds to the change, and processes the new row in a message flow.

Processing options on completion

When the message flow has processed an event, the flow can handle the event in the following ways:

  • Delete the event. Use this option if you do not want to store the event for future reference.
  • Update the status column. Use this option if you want to keep a record of processed events. Your event table must have a status column.
  • Archive the event to a separate event table. Use this option if you want to keep a record of events while keeping the event table to a minimal size.