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

For a database management system to be operatively coupled to a data processing system having memory for storing a database, a computer program product for directing the data processing system to process a record to be inserted into the database, the database comprising a plurality of base tables, the computer program product comprising:

• a computer readable medium tangibly embodying computer executable code for directing the data processing system, the computer executable code comprising:
• code for making a record copy matching the record;
• code to be executed for each base table to be selected from the plurality of base tables, the code to be executed comprising:
• code for 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
• code for 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.

For a database management system to be operatively coupled to a data processing system having memory for storing a database, a method for inserting received data into the database through a view constructed from a union of two or more base tables, 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.

For a database management system to be operatively coupled to a data processing system having memory for storing a database, a computer program product fir directing the data processing system to insert received data into the database through a view constructed from a union of two or more base tables, 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.