Asynchronous acid event-driven data processing using audit trail tools for transaction systems

• US 8,332,349 B1
• Filed: 01/06/2012
• Issued: 12/11/2012
• Est. Priority Date: 01/06/2012
• Status: Active Grant

• Current Assignee: Advent Software Incorporated (SS&C Technologies Holdings Incorporated)
• Original Assignee: Advent Software Incorporated (SS&C Technologies Holdings Incorporated)
• Inventors: Wilson, Thomas R.
• Primary Examiner(s): Jalil, Neveen Abel
• Assistant Examiner(s): KINSAUL, DANIEL W
• Application Number:
  US13/345,527
• Time in Patent Office: 340 Days
• Field of Search: None
• US Class Current: 707/607
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06Q 40/06 Asset management; Financial planning or analysis

Abstract

An audit system structured for auditing at least one operational table of a transaction system during an audit event is provided. In an embodiment, the audit system includes at least one audit history table operatively associated with the operational table of the transaction system, and each audit history table includes at least one database trigger configured for monitoring one or more data changes in the operational table. The database trigger generates an XML message for a monitored data change and transmits it to an SQL server service broker queue, which activates a procedure to transmit the message to at least one subscriber service broker queue, which activates an asynchronous data processing service for the monitored data change.

Claims

1. An audit computer system structured for auditing at least one operational table of a financial transaction computer system for an audit event, the audit computer system comprising:

(a) a programmable processor; and

(b) a computer memory in electronic communication with the processor, the memory configured to hold data and instructions, comprising;

at least one audit history table operatively associated with the operational table of the transaction computer system, each audit history table including at least one database trigger;

the audit history table being configured to store substantially all data changes which occur in the financial transaction computer system during the audit event by storing the data changes in the audit history table as audit event data;

the audit history tables being configured to function separately from the operational tables of the transaction computer system;

at least one audit event table operatively associated with at least one of the audit history tables and the associated operational table, the audit event table being configured for associating the data changes with the audit event, wherein the audit event table groups together a plurality of data changes that occur against multiple tables where the data changes produce a single logical action and track the plurality of data changes in an audit event table;

at least one representation of the data of an operational table as it appeared on a historical date generated by the operational interaction of the at least one audit history table, the at least one audit event table, and the at least one operational table;

the database trigger being configured to monitor for one or more data changes in the operational table, and to generate a message describing a monitored data change, and to transmit the message to a SQL server service broker queue;

the SQL server service broker queue configured, upon the arrival of the message at the queue, to activate a routing procedure to transmit the message to at least one applicable subscriber'"'"'s service broker queue; and

the subscriber service broker queue configured, upon arrival of the message, to activate an asynchronous data processing service for the monitored data change.

2. The audit computer system of claim 1, wherein the message is an XML message, and the SQL server service broker queue is an SQL server service broker queue in an SQL server.

3. The audit computer system of claim 1, wherein the transaction computer system includes a financial transaction computer system.

4. The audit computer system of claim 1, further comprising the audit history tables being configured to store the data changes by category.

5. The audit computer system of claim 4, further comprising the audit event table being configured to track at least one of a user who made the data changes, a module used to make the data changes, a time the data changes were made, a date the changes were made, or a job identifier.

6. The audit computer system of claim 1, the memory further comprising a table configured to track all operational tables associated with the audit event to minimize operational overhead for maintaining historical information and audit event data.

7. The audit computer system of claim 1, further comprising the audit history tables being configured to maintain at least one globally unique sequence number in association with the data changes of the audit event.

8. The audit computer system of claim 1, the memory further comprising an audit trail viewer operatively associated with the audit history tables, the audit trail viewer being configured for displaying at least one summary of the audit event data.

9. The audit computer system of claim 8, further comprising the audit trail viewer being configured for displaying “

before” and

“

after”

data values.

10. The audit computer system of claim 8, further comprising the audit trail viewer being configured for querying the audit system by at least one of a category, a user, a time period, or a module.

11. The audit computer system of claim 8, further comprising the audit trail viewer being configured to provide a detailed overview of field-level data changes that occurred in connection with the audit event.

12. The audit computer system of claim 8, further comprising the audit trail viewer being configured to permit cut and paste data operations from sections of the audit trail viewer.

13. The audit computer system of claim 8, wherein the summary includes a rolled-up summary.

14. The audit computer system of claim 1, further comprising the audit computer system being configured for exception-based auditing.

15. The audit computer system of claim 1, further comprising the audit computer system being configured with the capability to undo the data changes associated with the audit event.

16. The audit computer system of claim 1, further comprising the audit computer system being configured to generate at least one notification based on identification of a business situation in the audit event data.

17. The audit computer system of claim 1, wherein the audit history table stores the data changes using a row-versioning method for storing the data changes in the audit history table as audit event data.

18. A method using a computer system for auditing at least one operational table of a financial transaction computer system for an audit event, the method comprising:

monitoring with a computer system one or more data changes in the operational table of the transaction computer system using at least one database trigger operatively associated with an audit history table;

grouping, together, using the computer system, a plurality of data changes that occur against multiple tables where the data changes produce a single logical action and track the plurality of data changes in an audit table;

generating at least one representation of the data of an operational table as it appeared on a historical date by the operational interaction of the audit history table, the audit event table, and the at least one operational table;

storing substantially all data changes which occur in the financial transaction computer system during, the audit by storing the data changes in the audit history table as audit event data; and

permitting the audit history tables to function separately from the operational tables of the transaction computer system;

generating, using the database trigger, a message describing a monitored data change, and transmitting the message to a SQL server service broker queue;

activating, using the SQL server service broker queue, upon the arrival of the message at the queue, a routing procedure to transmit the message to at least one applicable subscriber'"'"'s service broker queue; and

activating, using the subscriber service broker queue, upon arrival of the message, an asynchronous data processing service for the monitored data change.

19. The method of claim 18, wherein the message is an XML message, and the SQL server service broker queue is an SQL server service broker queue in an SQL server.

20. The method of claim 18, further comprising storing the data changes by category.

21. The method of claim 18, further comprising tracking at least one of a user who made the data changes, a module used to make the data changes, a time the data changes were made, a date the data changes were made, or a job identifier.

22. The method of claim 18, further comprising storing the data changes using a row-versioning method for storing the data changes in the audit history table as audit event data.

23. A non-transitory computer-readable medium including instructions for auditing at least one operational table of a financial transaction computer system for an audit event, the medium comprising:

instructions for monitoring one or more data changes in the operational table of the transaction computer system including at least one database trigger operatively associated with an audit history table;

instructions for storing substantially all data changes which occur in the transaction computer system during the audit event including instructions for storing the data changes in the audit history table as audit event data;

instructions for permitting the audit history tables to function separately from the function of the operational tables of the financial transaction computer system;

instructions for grouping together a plurality of data changes that occur against multiple tables where the data changes produce a single logical action and track the plurality of data changes in an audit event table;

instructions for generating at least one representation of the data of an operational table as it a appeared on a historical date by the operational interaction of the audit history table, the audit event table, and the at least one operational table;

instructions for generating, using the database trigger, a message describing a monitored data change, and transmitting the message to a SQL server service broker queue;

instructions for activating, using the SQL server service broker queue, upon the arrival of the message at the queue, a routing procedure to transmit the message to at least one applicable subscriber'"'"'s service broker queue; and

instructions for activating, using the subscriber'"'"'s service broker queue, upon arrival of the message, an asynchronous data processing service for the monitored data change.

24. The non-transitory machine readable memory medium in claim 23, wherein the message is an XML message, and the SQL server service broker queue is an SQL server service broker queue in an SQL server.

25. The non-transitory machine readable memory medium in claim 23, further comprising instructions for storing the data changes using a row-versioning approach for storing the data changes in the audit history table as audit event data.