Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2001-03-26
2004-12-07
Pardo, Thuy N. (Department: 2175)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000, C707S793000, C707S793000, C707S793000, C707S793000
Reexamination Certificate
active
06829616
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Field of the Invention
The present invention relates to a method, system, and program for implementing a database trigger.
2. Description of the Related Art
Data records in a relational database management system (RDBMS) are maintained in tables, which are a collection of rows all having the same columns. Each column maintains information on a particular type of data for the data records which comprise the rows. Certain database programs, such as the International Business Machines Corporation (“IBM”) DB2 database program, include a trigger feature. A trigger defines a set of actions to perform when the database program modifies data in a specified database table. Triggers are used to perform functions such as validation of input data, automatically generate a value for a newly inserted row, read from other tables for cross-referencing purposes, write to other tables for audit-trail purposes, and support alerts through electronic mail messages. Triggers permit faster application development, global enforcement of business rules, and easier maintenance of applications and data.
Triggers are optional and are defined using the CREATE TRIGGER statement. A trigger may be defined with the following criteria. A “base table” is the table for which the trigger is defined. The “trigger event” defines a specific SQL operation that modifies the base table. The “trigger event” may comprise, for example, a delete, insert or update operation with respect to one or more columns of the base table. The “trigger activation time” defines, for example, whether the trigger should be activated before or after the trigger event is performed on the base table. The “triggered action” consists of an optional search condition and a set of SQL statements that are executed whenever the trigger event occurs.
The triggered action may operate on data from columns in the set of affected rows of the base table. Data from the set of affected rows in the base table are carried over to the triggered action through the use of transition variables. Transition variables use the names of the columns in the base table qualified by a specified name that identifies whether the reference is to the old value (prior to the update) or the new value (after the update). The new data can also be modified when subject to the triggered action. For instance, upon the occurrence of the trigger event, data from the base table subject to the SQL triggering action may be written to another table. In such case, the data to copy over as part of the triggered action is stored in a transition variable. This transition variable is then provided to the SQL update statement that implements the triggered action. Transition variables maintain their data in a work file. Further details of a database trigger are described in the IBM publication, “IBM DB2 Universal Database: SQL Reference, Version 6,” IBM document no. SC09-2847-00 (Copyright IBM, 1999), which publication is incorporated herein by reference in its entirety.
A large object (LOB) is a data type that contains large amounts of data, such as images, movies, audio, etc. In the prior art, a large object (LOB) that is a transition variable cannot be stored in the same work file that stores non-LOB transition variables due to the potentially large size of the large object (LOB) data.
Thus, there is a need in the art for improved techniques for maintaining large objects (LOBs) as transition variables.
SUMMARY OF THE PREFERRED EMBODIMENTS
Provided is a method, system, and program for implementing a database trigger. Upon detecting a trigger event, a determination is made of at least one row in a base table affected by the trigger event. For each column in the base table of a data type that is referenced in a triggered action associated with the trigger event, a reference is generated referencing the data of the data type. The reference is inserted in a transition table column including data of the data type from the affected row in the base table referenced in the triggered action. The reference is used to access the data of the data type when performing the triggered action.
In further implementations, the reference includes a row identifier and version number of the data in the base table column referenced by the triggered action.
Still further, the data type for which the reference is generated comprises a large object data type. In such case, the reference is used to access the large object data by accessing an auxiliary table storing large object data for the base table column referenced by the triggered action. The row identifier and the version number in the reference are used to access the large object in the auxiliary table. The row identifier and version number identify the location of the large object data in the auxiliary table.
In the described implementations, a reference to data of a particular data type, such as a large object (LOB) data type, is stored in a transition table used by the triggered action instead of the actual data itself to reduce the space in the transition table needed to represent the LOB data and to maximize the number of transition variable rows that may be maintained in the transition table.
REFERENCES:
patent: 5742810 (1998-04-01), Ng et al.
patent: 5857203 (1999-01-01), Kauffman et al.
patent: 5873105 (1999-02-01), Tremblay et al.
patent: 5890160 (1999-03-01), Hembry
patent: 5924100 (1999-07-01), Chang et al.
patent: 5930795 (1999-07-01), Chen et al.
patent: 5963936 (1999-10-01), Cochrane et al.
patent: 6061689 (2000-05-01), Chang et al.
patent: 6122640 (2000-09-01), Pereira
patent: 6144970 (2000-11-01), Bonner et al.
patent: 6374236 (2002-04-01), Chen et al.
patent: 6418448 (2002-07-01), Sarkar
patent: 2030781 (1995-03-01), None
IBM, Corp. “Enforcing Business Rules with Constraints and Triggers” [online], date unlisted, pp. 1-7. [Retrieved on Jan. 4, 2001].Retrieved from the Internet at URL: <http://www-4.ibm.com/cgi-bin/db2www/data/db2/ubd/winos2unix/support/document.d2w/report?fn=db2v7y0db2y043.htm#HDRKEYCON>.
IBM, Corp. “Mapping a Relational Database to a Hierarchical File System”, IBM Technical Disclosure Bulletin vol. 38 No. 10, Oct. 1995, pp. 309-311.
IBM, Corp. “SQL Reference”, from IBM DB2 Universal Database, Version 6, 1999, pp. 19-22, pp. 690-699 and pp. 1085-1088.
Pirahesh, H.Object—Oriented Features of DB2 Client//Ever. Proceedings of the 1994 ACM SIGMOD International Conference on Management of Data. SIGMOD Record, vol. 23, Issue 2, Jun. 1994, 3 pages.
Biliris, A.An Efficient Database Storage Structure for Large Dynamic Objects. Eighth International Conference on Data Engineering, Feb. 2-32, 1992, Issue XVI, pp. 301-308. Sponsored by IEEE Computer Society Technical Committee on Data Engineering.
Riley, M.F., et al.The Design of Multimedia Object Support in DEC Rdb. Digital Technical Journal., vol. 5, No. 2, Spring 1993, pp. 50-64.
O'Connnell, W., et al.Optimizer and Parallel Engine Extensions for Handling Expensive Methods Based on Large Objects. 15thInternational Conference on Data Engineering, Mar. 23-26, 1999, Sydney, Australia, pp. 304-313. Sponsored by IEEE Computer Society Technical Committee on Data Engineering.
Chen Yao Ching Stephen
Cornwell Karelle Lynne
Dang Tammie
Moore Beth Rivenes
Shyam Kalpana
International Business Machines - Corporation
Konrad Raynes & Victor LLP
Pardo Thuy N.
Victor David W.
LandOfFree
Method, system, and program for implementing a database trigger 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, system, and program for implementing a database trigger, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method, system, and program for implementing a database trigger will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3336031