Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1999-09-27
2002-06-11
Mizrahi, Diane D. (Department: 2175)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000
Reexamination Certificate
active
06405212
ABSTRACT:
FIELD OF THE INVENTION
The present invention relates to database management systems, and more particularly, to database management systems in which selectable system events trigger programmable actions upon satisfaction of programmable conditions.
BACKGROUND OF THE INVENTION
Databases are organized arrangements of information on computer systems convenient for storing and retrieving information. Database management systems provide resources for conveniently creating, maintaining, deleting and protecting access to databases. In a relational database system a single database is made up of one or more related tables, each table having information arranged in rows and columns. Each column represents a kind of information and each row stores values for all the columns in the table.
Users interact with a database using a query language such as the Structure Query Language (SQL). SQL is a powerful non-procedural language which serves as the industry standard for relational database management systems. Data Manipulation Language (DML) statements are SQL statements that retrieve or manipulate data in tables. DML statements are the most frequently used SQL statements. Data Definition Language (DDL) statements are SQL statements that define, alter the structure of, and drop tables.
In a database management system, a trigger is an object that specifies a series of actions to be automatically performed when a specific event occurs. According to industry standards, DML statements are the events that cause user-defined triggers to be activated (or “fired”). For example, in a relational database, user-defined triggers may be designed to fire when a row of a database table or a table view is updated, inserted, or deleted. Accordingly, each user-defined trigger is typically associated with a single database table. That is, in a conventional database management system, the scope of the user-defined trigger is the table level of the database.
The series of actions specified by a trigger is typically written as instructions in a high-level database language such as SQL or PL/SQL (a procedural language extension of SQL available from Oracle Corporation of Redwood Shores, Calif.). In conformance with industry standards, these instructions must be able to access the data values of table columns corresponding to an affected row before the triggering DML statement was applied (the “old values”) and after the modification was applied (the “new values”).
Since triggers are objects, database customers can define, remove, and store triggers associated with a database table, and the database management system keeps track of which triggers have been defined for which table by storing that information as metadata (information about data) associated with the table in a data dictionary for the database. Consequently, triggers enable database customers to implement additional functionality in their databases for such purposes as enforcement of business rules and security.
Conventional trigger mechanisms can be wasteful of system resources. Specifically, triggers often include conditions. When a trigger is fired, the condition associated with the trigger is evaluated. If the condition is satisfied, then some specified action is performed. If the condition is not satisfied, then no action is performed. Thus, the number of times a trigger fires may be magnitudes greater than the number of times the action associated with the trigger actually has to be performed.
When the triggering event has occurred, substantial system resources are required to evaluate the condition. For example, to evaluate any allowed SQL condition, code encompassing about 20,000 instructions must be executed. This consumption of resources is exacerbated as the number of triggers associated with each event increases. For example, if a database has 1000 users, and each has defined 3 triggers for a “delete” operation on the same table, then 3000 triggers fire when a delete event occurs on that table, and 3000 conditions have to be evaluated, each requiring the execution of code with 20,000 instructions. This evaluation load is the same whether or not the conditions are satisfied. Thus it is possible to consume substantial resources just to conclude that few or no actions are to be executed.
Trigger conditions and actions are procedures typically stored with table metadata in a data dictionary within the database. Trigger events are confined to table and view level DML operations insert, update, delete. When a user defines a trigger, the user specifies the event name. The event name must be chosen from a short list of selectable events. The selectable events are INSERT, UPDATE and DELETE. The user must then specify the table name, e.g., EMPLOYEE if the employee table is to have the trigger. Then the user provides the condition as an SQL expression and the action as a stored procedure.
The trigger is then fired when the user executes an SQL statement that performs the selected DML operation on the specified table. When the table is referenced in an SQL statement as the result of a database tool or a user application process run at a later time by the same or a different user, the table metadata is loaded into memory, including a map of all the table triggers and events on which they fire. The trigger events are compared to the SQL statement to see if the trigger is applicable, i.e., if the SQL statement includes the DML operation that is the event for the trigger, e.g. the trigger is for INSERT and the SQL statement includes the INSERT operation for this table. If the trigger is applicable, then the condition and action statements are loaded into a cache in dynamic memory from disk, if not already there, compiled if necessary, and combined with the rest of the compiled SQL statement. Then when the compiled SQL statement is executed, the trigger condition is evaluated using the available old and/or new values on the row. If the condition is satisfied, the action is executed, again using the old or new values if needed.
There are many database system states and system operations for which the conventional system does not allow customers to define triggers. These states and operations can be important to the customer but have a scope beyond that of the table level. For example, a user of several databases may wish to respond to a particular database starting up. As another example, the owner of a table in a database may want to respond to a particular user logging on. As further examples, the user may wish to respond to the amount of available volatile storage, to the occurrence of a particular DDL statement, to the occurrence of an error condition, or to the state of a queue of DML operations. With the conventional database management system, the customer can not define triggering events based on such states and operations beyond the scope of the table level.
One technique for customers to define actions that are performed upon the occurrence of such important situations involves polling the database by periodically sending requests from a database application to the database server that manages the database. In polling, the state of the database (as recorded, for example, in dynamic performance tables) is provided to the database application in response to a specific request for that information. The information thus provided to the database application may then be used by the database application to determine whether to issue certain commands to the database server.
With polling, the request is repeated at regular intervals of time dependent upon a polling frequency. If the polling frequency is very high, the polling will consume many system resources and interfere with the performance of the database being tracked. If the polling frequency is very low, important events may be discovered too late, or missed altogether. Thus polling can be expensive, unreliable, or both.
There is a need to reduce the consumption of system resources in applying triggers fired at the table level upon DML operations. There is also a need for users to respond to system
Jain Namit
Samu Sriram
Wang Wei
Hickman Brian D.
Hickman Palermo & Truong & Becker LLP
Mizrahi Diane D.
Oracle Corporation
LandOfFree
Database system event triggers does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Database system event triggers, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Database system event triggers will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-2931250