(Show Contents)

ARCHIBUS Web Central

Recording Data Change Events

Using Data Change event listeners, application-level Java code (e.g. the Space Inventory activity workflow rules) may register callbacks on startup with the datasource. The core would invoke this callback if:

• A datasource on a particular table inserted, updated, or deleted a record in the specified table.
• A datasource performed a bulk query (e.g. UPDATE … WHERE …) against that table.

Single-record Changes

For individual records, the core would provide that callback function with these parameters:

• The table being changed (in case the same WFR handles multiple tables).
• The primary key of the changed record.
  
• The new field values for that record.
  
• The old values for the changed fields of that record if the user changed a primary key value and did not use direct SQL.
• The user (afm_users.name) of the account whose session request
• The date and time on the application server of when the change was made.
  

Bulk Changes

For bulk changes, the core would provide that callback function with these parameters:

• The table that the bulk change affects (e.g. “wr”). This may be null if the core does not know which table was affected.
• The content of the SQL that made the change, [e.g. "DELETE FROM wo WHERE description = "desc” and wo_id=”1234433”]
• The user name, date, and time, as above.

The callback can optionally decide to log this change or inspect the SQL statement textually to determine if it needs to perform any other action.

The chief use case for adding logging on bulk changes would be complete audit logging and debugging.

Other Conditions

Some other conditions are:

• Multiple applications can register callbacks on the same table. The core will call them in sequence. The core would not guarantee to call them in any order.
• The callback cannot cancel the triggering change.

Transactions and Commits

If you are writing application-level logic, such as the logic that synchronizes the Workspace Transactions table with the Rooms table, whether to commit at any point is an application-level decision. Some application-level rules, such as those that create autonumbered records, must commit as a part of the logic.

If you are writing a callback for audit-logging purposes, do not commit. If the transaction is canceled, the originating change – as well as any records you wrote to the log – will be rolled back in conjunction if one of the database transactions fails.

Cascading Updates

Some user actions trigger cascading updates to the database. For instance, if you delete a room, the product will clear all dependent references to that room in the other tables, e.g. the equipment, employee, work order, etc. tables. If you rename a room, the product will update all dependent tables.

The data change event notification fires only on the root record of the cascading change. For instance, in the case above, the data change event will fire when the product updates the room record, and your data change event handler will get a notification that a particular primary key record in the room table was deleted or updated. However, your handler will not receive notification that the equipment, employee, work order, etc. tables have changed.

To take another example, suppose you delete a building record. This will also delete all floor and room records as well as clear all references to that building, those floors, and those rooms throughout the database. However, your handler will receive notice only that the root building with a particular primary key was deleted.

This is the correct notification choice for two reasons. First, the root record is the one with the most precise information. In the case of cascading updates, the product knows which particular table and key is being changed. The application knows of the relations between tables and can react to the change as needed. Second, this behavior is consistent across all databases -- whether the cascading change is made in the database server itself (as is the case for Sybase) or in the ARCHIBUS Web Central server application (as is the case for Microsoft SQL Server and Oracle).

Use Cases

Changing Room’s Owner-of-Record

User changes the Division and Department in the Rooms table to change the department that is the “owner of record”. The space domain provides a callback workflow rule that updates the transaction table (the Workspace Transactions table) to audit the change.

On being notified of a single-record update to a Room (rm) table record this WFR:

• Takes the appropriate action in the Workspace Transactions table based on whether the change was an insert, update, or delete. The WFR uses the primary key of the record (bl, fl, rm) and the new values (dv and dp) to do so.
• Records who changed the record (i.e. the afm_user.name) and when (Date and time on the app server).
• Does not need to know the previous dv and dp values, as these are already in the Workspace Transactions table.

On being notified of a bulk update to the Rooms table, this WFR:

• Logs the details of the bulk change to the database event log (the table, user, date, time and query).
• Sets the resynchRoomPercentagesTable activity parameter to Yes. Key reports and actions that depend on Space Transactions check this activity parameter before running and alert the user if the table needs to be resynched.

Only the CRE/FM department personnel make bulk changes – such as when they use Replace Column in the Smart Client or Edit Data Multiple in the Extension for AutoCAD. They also tend to make bulk changes on just the portion of the inventory that is new and is not yet managed using Space Transactions. As such, it’s appropriate to ask these users to resynch after making extensive changes to the Room inventory.

The use case to change a room’s Category and Type and flow the appropriate change down to the Workspace Transactions table involves the same process.

The use case to change an Employee’s Primary-Room assignment in the employees (em) table also is the same process.

See RoomTransactionDataEventListener

Deleting a Room Record

This use case illustrates the need for the notification before the deletion occurs.

If a user deletes a room record, the data change event needs to archive the historical information for that room record that is currently in the Workspace Transactions table before the room is deleted. Otherwise, the room is deleted and the change already cascades to the Workspace Transactions table -- deleting the information that would need to be archived.

See RoomTransactionDataEventListener

Changing an ARCHIBUS User’s Information

The system activity supplies a logging callback workflow rule that the core calls whenever a user changes a User, Role, or Process Assignment record.

User uses the Smart Client, the Web Central ARCHIBUS Users form, or the My Profile form to change an afm_user record.

The callback workflow rule logs the single-record or bulk update to the table in the database event log, by recording the table, name of the user making the change, the date and time, and

• the SQL that created a bulk update
  or
• the primary key, changed fields for a single-record update.

In the case of an inquiry, the system administrator can review the log.

See LoggerDataEventListener

Reporting Exceptions

There are a number of cases in which the site can bypass the change events, for instance if users at the site:

• Update the database using the Windows Client/Server program
• Update the database using direct SQL or by loading a database extract file

Sites that need to record these kinds of events should use the database transaction log or database triggers.

However, sites may wish to have an application-level exception report that detects when this kind of change has taken place.

For instance, consider the use case in which the Space Inventory activity has a workflow rule that flows Room table changes to the Workspace Transactions table. Suppose a site then imports a set of room records using direct SQL statements that bypass the ARCHIBUS core. The activity can create an workflow rule that finds exceptions by comparing the values in the Rooms table to the most recent record in the Workspace Transactions table. If this exception report finds a difference it can set the resynchRoomPercentagesTable activity parameter to Yes. Key reports and actions that depend on Space Transactions check this activity parameter before running and alert the user if the table needs to be resynched.

Usage Notes

• Intended Use: The intent behind the data change event handlers is to do targeted cleanup after users change inventory tables or to do auditing. The intent is not to have the handlers implement large amounts of application-level logic.

• Performance: If you write a data change event handler, make certain it executes very fast. Don’t query for data dictionary information or other heavy information. Keep the list of tables that your handler acts on as small as possible.
• Threads: Do not create new threads of execution in workflow rules, such as a long-running job. This rule goes double for data change event handlers.
• Commits: You may wish to avoid doing any commit inside of data change event handlers. In general, there is no reason to do so. The workflow rule container will commit in any case when any workflow rule ends, so there is no need to. If the parent workflow rule encounters an error and rolls back, the concomitant data change event handler changes will roll back as well.
  • The handler cannot assume that the parent workflow rule does not commit. The parent workflow rule may be updating a large number of records, and will need to commit for performance reasons. Or the parent workflow rule will need to commit to propagate some changes – such as when a workflow rule commits after the creation of autonumbered records in Oracle so that later portions of the workflow rule code can refer to that particular autonumbered records.
  • Keep in mind that if the parent workflow rule throws an error after the data change event handler fires, the data change event handler logging will not be in the log. If you need to track not just all changes but all attempts to change, write a custom handler for that exception.