System, method, and programs for automatically building audit triggers on database tables

Abstract

A method and system for building audit triggers on database tables is presented. The method includes compiling at least one informational table containing names of one or more source tables to be audited, constructing at least one build trigger having sufficient functionality for building audit triggers on the source tables, and installing the informational table and build trigger in a database environment in which the build trigger instructs a database engine to build audit triggers on the source tables.

Description

BRIEF DESCRIPTION OF THE DRAWINGS

The present invention is illustrated by way of example, and not by way of limitation, in the figures of the accompanying drawings and in which like reference numerals refer to similar elements and in which:

FIG. 1 is a flowchart illustrating the steps involved in an exemplary process of creating and executing a build trigger that exists on a sole informational table, in accordance with an embodiment of the present invention;

FIG. 2 is a flowchart illustrating an exemplary process of constructing and executing an audit trigger comprising a designations table to hold source table names and a rules table to hold rules that dictate how the trigger is constructed, in accordance with an embodiment of the present invention;

FIGS. 3 through 6 illustrate an exemplary system for automatically building audit triggers on a database, in accordance with an embodiment of the present invention. FIG. 3 shows an exemplary process that occurs when a programmer alters information in either the designations table or the rules table and causes one or more audit triggers to be built on the source tables of the audit operation. FIG. 4 shows an exemplary process that occurs when a user alters data in an audited table that contains an audit trigger built by the process shown by way of example in FIG. 3. FIG. 5 shows an exemplary process that occurs when a programmer alters the information in either the designations table or the rules table and causes the maintenance procedure to be built or rebuilt; and

FIG. 6 shows an exemplary process that occurs when a maintenance procedure is scheduled that archives audit data, discards audit data or both.

Claims

1. A method for building audit triggers on database tables, the method comprising the Steps of: compiling at least one informational table comprising names of one or more source tables to be audited;constructing at least one build trigger comprising sufficient functionality for building audit triggers on said source tables; andinstalling said informational table and build trigger in a database environment in which said build trigger instructs a database engine to build audit triggers on said source tables.

2. The method as recited in claim 1, further comprising constructing additional functionality for generating audit tables for receiving data from the audit triggers.

3. The method as recited in claim 1, further comprising constructing additional functionality to build, rebuild or delete audit triggers when data in said informational table is changed.

4. The method as recited in claim 1, further comprising providing a rules table comprising rules for interpretation by said functionality in building the audit triggers.

5. The method as recited in claim 4, further comprising constructing additional functionality to build, rebuild or delete audit triggers when data in said rules table is changed.

6. The method as recited in claim 1, further comprising constructing additional functionality for scheduling building of the audit triggers.

7. The method as recited in claim 1, further comprising constructing further functionality for scheduling maintenance procedures for the data in said audit tables to govern when data is archived and/or discarded.

8. A method for building audit triggers on database tables, the method comprising: steps for providing at least one informational table;steps for constructing at least one build trigger; andsteps for installing said informational table and said build trigger in a database environment in which said build trigger instructs a database engine to construct the audit triggers.

9. The method as recited in claim 8, further comprising steps for providing a rules table for said build trigger.

10. The method as recited in claim 9, further comprising steps for providing a functionality to build, rebuild or delete audit triggers when data in said rules table is changed.

11. The method as recited in claim 8, further comprising steps for providing a functionality to build, rebuild or delete audit triggers when data in said informational table is changed.

12. The method as recited in claim 8, further comprising steps for generating one or more audit tables.

13. The method as recited in claim 8, further comprising steps for providing functionality for scheduling building of the audit triggers.

14. The method as recited in claim 8, further comprising steps for providing maintenance procedures.

15. A system for building and using audit triggers on database tables, the system comprising: means for constructing at least one informational table;means for constructing a build trigger;means for installing said informational table and said build trigger in a database environment where audit triggers are constructed; andmeans for accessing the database tables and activating audit triggers.

16. The system as recited in claim 15, further comprising means for providing a rules table.

17. The system as recited in claim 15, further comprising means for generating one or more audit tables.

18. The system as recited in claim 15, further comprising means for providing maintenance procedures.

19. The system as recited in claim 15, further comprising means to build, rebuild or delete audit triggers when data in said informational table is changed.

20. The system as recited in claim 16, further comprising means to build, rebuild or delete audit triggers when data in said rules table is changed.