Method of executing before-triggers in an active database

Details Method of executing before-triggers in an active database Method of executing before-triggers in an active database

2001-03-29

2004-06-01

Mizrahi, Diane D. (Department: 2175)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000

Reexamination Certificate

active

06745174

ABSTRACT:

FIELD OF THE INVENTION
The present invention relates generally to executing triggers in active relational databases and more specifically to the execution of before-triggers in a relational data base management system.
DESCRIPTION OF THE RELATED ART
Database management systems (DBMS)
11
, such as the system shown in
FIG. 1
, have become the dominant means of keeping track of data, especially for servers connected to the Internet. These systems take an organized approach to the storage of data by imposing a data model, typically a relational data model, on the data
17
that is stored in the database
15
. Included in the typical DBMS are a Query Processing Engine
13
, a File Access and Storage Management subsystem
21
for accessing the database
15
, a Concurrency Control subsystem
19
for managing locks needed for concurrency on database items (tables and rows) and a Recovery Control Subsystem
23
for restoring the DBMS
23
to a consistent state after a fatal error. The latter two subsystems
19
,
23
, are interconnected with the File Access and Storage Management subsystem
21
.
In the relational data model, data is stored as a relation, which has two aspects, the relation schema and the relation instance. The relation schema specifies the relation's name, and the name and domain of each column in the relation. The relation instance is a set of records (also called rows or tuples) that conform to the relation schema. A relation instance is therefore a table of records, each of which has a column that meets the domain constraints imposed by the schema.
Not only does the DBMS impose a constraint on storage of data, a DBMS usually formalizes the means by which information may be requested from the database. In particular, a query language is specified by which questions may be put to the database. The language is usually based on a formal logic structure such as relational algebra or calculus. Queries are usually carried out in the DBMS
11
by a Query Processing Engine
13
, which has a number of components for parsing a query, creating a query plan, and evaluating the query plan. In particular, a component of the Query Processing Engine
13
, a Query Optimizer, creates one or more query plans, each in the form of a tree of relational operators, that are evaluated for execution of the query based on some efficiency metric.
Relational operators take one or more tables as inputs and generate a new table as the output. For example, a selection operator selects one or more rows of an input table meeting the selection criteria to produce an output table having only those rows. Operators can be composed since an operator may take as input a table generated as the output of another operator. A tree of operators is the representation of a composition of the relational operators appearing as the nodes of the tree.
A tree of such operators for a particular query plan is shown in FIG.
3
. As can be observed from the tree of
FIG. 3
, relational operators are connected to each other and to base tables T
1
and T
2
by means of queues Q
1
-Q
4
. These queues supply input rows to a particular operator and store output rows from the operator. The queues allow an operator to start processing rows as soon as the operator that supplies the rows begins to produce them and before all rows are produced. Such pipelining improves the efficiency of the system because intermediate results need not be stored in a temporary table and then read again for input.
The standard language for implementing a DBMS is the Structured Query Language (SQL). This language includes Triggers, which are actions executed by the DMBS under certain conditions.
A database having a set of triggers is called an active database and each trigger in the database has three parts, an event, a condition and an action. The event part is a change to the database, such as an insertion, deletion, or modification of a table, that activates the trigger. The SQL statement which is the activating event, is termed the activating statement. A condition is a test by the activated trigger to determine whether the trigger action should occur and an action is an SQL statement that is executed if the trigger event and trigger condition are both satisfied. The set of rows affected (i.e., inserted, updated, or deleted) by the activating statement is termed the affected set of rows for the relevant trigger.
The action part of the trigger can occur either before or after the activating statement. If before, it is called a before-trigger and if after, it is called an after-trigger. In addition, triggers can operate at the row level or the statement level. A statement trigger executes its action once per activating statement and a row trigger executes its action for each row in the affected set. The combination of “before” and “after” with “row” and “statement” creates four different types of triggers. Chain reactions of trigger actions and recursive trigger actions are also possible.
The execution of triggers in a relational database is governed by the proposed ANSI standard for SQL (SQL:1999) which places certain restrictions on trigger execution. A chief restriction is that the triggers be executed serially in their creation time order or at least that the serial execution of triggers be equivalent in outcome and effect on the database to the execution of triggers in their creation time order. However, the serial execution of triggers including before-triggers, in accordance with the proposed ANSI:99 standard, would seriously affect the performance of the DMBS, especially if many before-trigger actions are involved. Thus, there is a need for the improved execution of multiple before-trigger actions for improved performance of such actions over a purely sequential execution, but still conforming to the ANSI standard.
BRIEF SUMMARY OF THE INVENTION
The present invention is directed towards the above need. A method, in accordance with the present invention, of forming an execution plan for a plurality of trigger actions in an active database includes determining the triggers activated by an activating statement, where the activated triggers are before-triggers. The method further includes forming an operator tree for the activating statement, where the activating statement includes a table-affecting operator and forming an action tree for each trigger action that is activated by the statement. Next, the table-affecting operator is removed from the activating statement operator tree and a tentative execution operator is created that includes any operations of the activating statement other than the table-affecting operator. A temporary table for accumulating rows affected by the tentative execution operator and the activated before triggers is then obtained. Following this a subtree is formed by interconnecting an insertion operator between the temporary table and a flow operator that is operative to receive the operator tree input rows and pipeline the rows to the insertion operator. The actions of the activated before-triggers and the tentative execution operation are then inserted into the flow between the operator tree input and temporary table and the table-affecting operator, which receives input from the temporary table, is then connected to the subtree for execution after the execution of the subtree.
Any row after-triggers that are activated by the activating statement are interconnected for pipelined execution with the table-affecting operator and any statement after-triggers are interconnected for execution subsequent to the activating statement.
An advantage of the present invention is that before triggers are executed as a combined trigger to reduce the execution time of the triggers compared to purely sequential execution of the before triggers.


REFERENCES:
patent: 5864842 (1999-01-01), Pederson et al.
patent: 5873075 (1999-02-01), Cochrane et al.
patent: 5875334 (1999-02-01), Chow et al.
patent: 5881232 (1999-03-01), Cheng et al.
patent: 5884299 (1999-03-01), Ramesh et al.
patent: 5890148 (1999-03-01), Bhargava et al.
pat

Affiliated with

Also associated with

Rating

Method of executing before-triggers in an active database does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Method of executing before-triggers in an active database, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method of executing before-triggers in an active database will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3341556