Method, system and computer program for inserting records into a database

• US 7,860,845 B2
• Filed: 07/23/2007
• Issued: 12/28/2010
• Est. Priority Date: 08/20/2004
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: International Business Machines Corporation
• Inventors: Qian, Xiaoyan, Chen, Zhimin, Rielau, Serge Philippe, Liu, Linqi
• Primary Examiner(s): LeRoux; Etienne P
• Assistant Examiner(s): Witzenburg; Bruce A
• Application Number:
  US11/781,841
• Publication Number:
  US 20070288492A1
• Time in Patent Office: 1,254 Days
• Field of Search: 707/609, 707/999.001, 707/999.002, 707/999.005, 707/999.1, 707/687, 707/690, 707/691, 707/702
• US Class Current: 707/702
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

For a data processing system having memory for storing a database, a method, a system and a computer program product for directing the data processing system to process a record to be inserted into the database is disclosed. The database includes a plurality of base tables. The method includes the steps of making a record copy matching the record, for each base table to be selected from the plurality of base tables: providing a base table candidate indication for a selected base table, the base table candidate indication indicating whether the selected base table is a candidate base table that may receive the record, the base table candidate indication being determined on an outcome of executing before triggers and an outcome of testing constraints in association with the record copy, the before triggers and the constraints being associated with the selected base table; and restoring the record copy so that the record copy matches the record before providing a next subsequent base table candidate indication for another base table to be selected.

Claims

1. A method for inserting received data into a database through a view constructed from a union of two or more base tables, the method implemented in a database management system to be operatively coupled to a data processing system having memory for storing the database, each base table having one or more constraints defining necessary conditions for at least one field of the data to meet for data to be inserted into the respective base table and including at least one before trigger operable to modify the data in a field before the insertion is performed, the method comprising the steps of:

for each field of the received data, adding a first column to the received data to each hold a copy of the original value of the field and copying the original values to the first column;

adding a table identifier column to the received data to hold an identifier of a base table and setting the value of each row of the table identifier column to a predefined value;

for each base table in turn, executing all before triggers defined for the base table on the received data and determining for each row of the received data if the values of the received data after the before triggers have been processed meet the identified constraints to insert the row of received data into the base table and, for each row;

(a) if the identified constraints are met, determining if the table identifier column for that row has the predefined value and;

(i) if it does not, marking the row with an error message indicating an ambiguous insert error and, (ii) if it does have the predefined value, then changing the value of the table identifier column to indicate the base table under consideration and copying the original value of each field from the first column to the respective field;

or

(b) if the identified constraints are not met, copying the original value of each field from the first column to the respective identified field ; and

after all base tables have been considered, inserting each row of received data with an indication of a base table in the table identifier column into the indicated base table having first processed all before triggers defined for the indicated base table.

2. The method of claim 1 further comprising the step of first, for each base table, identifying fields which can be modified by a before trigger defined for the base table and adding the first column only for those identified fields and copying original values to and from the first added column only for those identified fields.

3. The method of claim 1 further comprising the step of returning an ambiguous insertion error for each row of received data marked with an error message.

4. The method of claim 1 further comprising the step of returning a no target error message for each row of received data having the table identifier column still indicating the predefined value.

5. The method of claim 1 wherein the database employs structured query language as a control mechanism.

6. The method of claim 5 wherein the view is constructed from a union all operation on the base tables.

7. A computer program product for directing a data processing system to insert received data into a database through a view constructed from a union of two or more base tables, the computer program product configured for a database management system to be operatively coupled to the data processing system having memory for storing the database, each base table having one or more constraints defining necessary conditions for at least one field of the data to meet for data to be inserted into the respective base table and including at least one before trigger operable to modify the data in a field before the insertion is performed, the computer program product comprising:

a computer readable medium for tangibly transporting computer executable code for directing the data processing system, the computer executable code comprising;

code for adding a first column to the received data to each hold a copy of the original value of the field and copying the original values to the first column for each field of the received data;

code for adding a table identifier column to the received data to hold an identifier of a base table and setting the value of each row of the table identifier column to a predefined value;

code for executing, for each base table in turn, all before triggers defined for the base table on the received data and determining for each row of the received data if the values of the received data after the before triggers have been processed meet the identified constraints to insert the row of received data into the base table and, for each row;

(a) if the identified constraints are met, determining if the table identifier column for that row has the predefined value and;

(i) if it does not, marking the row with an error message indicating an ambiguous insert error and,

(ii) if it does have the predefined value, then changing the value of the table identifier column to indicate the base table under consideration and copying the original value of each field from the first column to the respective field;

or

(b) if the identified constraints are not met, copying the original value of each field from the first column to the respective identified field; and

code for inserting each row of received data, after all base tables have been considered, with an indication of a base table in the table identifier column into the indicated base table having first processed all before triggers defined for the indicated base table.

8. The computer program product of claim 7 further comprising:

code for first identifying fields, for each base table, which can be modified by a before trigger defined for the base table and adding the first column only for those identified fields and copying original values to and from the first added column only for those identified fields.

9. The computer program product of claim 7 further comprising code for returning an ambiguous insertion error for each row of received data marked with an error message.

10. The computer program product of claim 7 further comprising code for returning a no target error message for each row of received data having the table identifier column still indicating the predefined value.

11. The computer program product of claim 7 wherein the database employs structured query language as a control mechanism.

12. The computer program product of claim 11 wherein the view is constructed from a union all operation on the base tables.