Patent Application
20070208692
The present invention relates generally to computer systems, and more specifically to managing relational databases.
Relational databases are well known today. They typically comprise tables organized into rows and columns. The first column lists “reference” items for respective rows, for example, names of specific customers in a table containing customer information. The “top” row identifies the type of data or “content item” for each row entry in the column, for example, the date of last update of the customer name, a hash value for the customer name and the customer's zip code, whether the customer name has been updated since it was first inserted, etc. As another example, the first column lists the names of employees as reference items in an table containing employee information, and the top row lists salary and last date of update of the salary as content items. The “inner” fields of both table include the actual data corresponding to each combination of reference item and content item. Specific data fields in each tables are accessed by referencing the corresponding row containing the reference item and the column containing the content item of interest.
Structure Query Language (“SQL”) is a standard programming language for program code that writes data into (i.e. inserting, adding or deleting data) the data fields, and reads data from the data fields (and also selects a data field or column, or finds a data field or column). SQL is also used to add, delete or change a reference item in the first column or add, delete or change a content item in the top row. Although SQL is both an ANSI and ISO standard, many database programs include nonstandard extensions to the standard SQL commands.
Some of the columns of a table in a relational database can be interrelated as a “family” or a “grouping”. In the example of the first table described above, when a row for a new customer is inserted or a customer name in an existing row is updated, the date of last update of the customer name, and hash value of the customer name and zip code should also be updated. In this first example, the customer name is a “parent” column, the date of last update is a dependent column and the hash value is a dependent column, all in the same family. In the example of the second table described above, when an employee is hired, the employee's salary will change (to the starting salary) as well as the date of update of the salary. In this second example, the name of the employee is a “parent” column, the salary is a “dependent” column and the date of last update is a dependent column, all in the same family.
The general term for an action(s) to perform, such as to update a dependent column, in response to a change to a parent column of the same family is “trigger program” or “trigger” for short. The trigger can automatically cause updates to the dependent columns, automatically generate or transform values for inserted or updated rows in the dependent table, or invoke functions to issue alerts in response to changes to the parent table. The trigger can be executed, in response to a request to change a parent column, shortly before an insert, update or deletion to the parent column or shortly after an insert, update or deletion to the parent column. A trigger can be written in SQL.
It was known to write a SQL program function that automatically updates a dependent column when there is a request to update its parent column. While this is effective to automatically update the dependent column, it was burdensome for a person to write this trigger program function and errors were made in the writing of the trigger program function, especially when there were a large number of parent and dependent columns and a large number of trigger programs to write.
Accordingly, an object of the present invention is to facilitate the process of generating a trigger program.
The present invention resides in a system, method and program for generating a trigger program that updates an entry in a dependent column in a table in response to a request to update a corresponding entry in a parent column in the table. A trigger program template is provided. The trigger program template includes program steps which indicate a type of update to an entry in an unspecified parent column which triggers an update to a corresponding entry in an unspecified dependent column. The trigger program template also indicates a type of the unspecified dependent column to update upon the update to the entry in the unspecified parent column. The table is automatically scanned to identify one or more dependent columns of the type. In response to identification of the one or more dependent columns of the type, a name of a parent column, corresponding to the one or more dependent columns, is inserted in the trigger program template steps to identify the corresponding parent column as the unspecified parent column and identify the one or more dependent columns as the unspecified dependent columns.
According to a feature of the present invention, names of the one or more dependent columns comprise the name of the specified parent column and respective suffices indicating the types of the dependent columns.
According to another feature of the present invention, one of the dependent columns records time stamps for when the update to the respective entries in the parent column were made.
The present invention will now be described in detail with reference to the figures.
Each of the predefined trigger function templates in database 50 also includes the trigger function program template for forming the actual trigger function program. Some steps of the trigger function program template include a variable; the name of the parent column of the same family is substituted for the variable to form the actual trigger function program from the template.
The following is an example of a predefined trigger function template in database 50:
According to this example, in response to a request to add or change a customer's last name in a parent, reference column “CUSTOMER_LNAME”, the time stamp row in the _TS dependent, item column is updated with the current time and the customer status row in the _CS dependent, item column is updated to record “updated” as the status of the customer name. Because the data portion of the trigger function template indicates “BEFORE INSERT” type of trigger, the time stamp and customer status are updated in the table after the request is made to update the customer's last name but shortly before the customer's last name is updated in the table.
Also in accordance with the present invention, computer 10 includes an automatic trigger generation program 40 which automatically generates trigger programs based on the trigger program function templates described above. Program 40 generates the trigger programs by automatically identifying the parent columns, i.e. those with a predefined or less number of characters and for which there are other columns having the same name and an additional suffix, based on the naming convention. After identifying the parent columns, program 40 fills-in the name of each parent column in the trigger function template for the variable, and then copies the resultant trigger function program to storage for subsequent usage.
During operation, the trigger function program will perform its trigger function on data entries of the dependent columns in response to activity in the respective row of the parent column. Other types of trigger functions can invoke functions to issue alerts in response to changes in the parent column. Often times, a single trigger function template can be used for multiple parent columns and dependent columns, those dependent columns having the same suffix as listed in the trigger function program. Consequently, when new tables are added to database 30 or new columns are added to existing tables, often times, program 40 can use an existing trigger function template in database 50 to automatically generate the trigger function program. There is no need to manually write the trigger function program or even manually identify the dependent columns for each parent column.
After generating the trigger function program for this family, program 40 loops back to decision 110, yes branch, step 120 and step 130 to generate the trigger function program for the next family, if any, in the table. After all the families in this table have been processed (decision 110, no branch), i.e. the trigger function programs have been generated for all the families in this table, program 40 loops back to decision 102 to determine if there is another or next table yet to be processed. If so (decision 102, yes branch), program 40 proceeds to steps 104, 106, 110, 120 and 130 to generate the trigger function programs for the families in this other table. After all the trigger function programs are generated for all of the families in all of the tables in database 30, program 40 copies all the trigger programs to a file to allow an administrator to view or test them before activating them (step 140). Next, program 40 completes creation of the trigger function programs by executing a known create trigger SQL program which installs the trigger function programs into a system catalog table 91 (step 160). Consequently, when new trigger events occur, a trigger manager 95 (which is typically software such as found in IBM DB/2 software) compares them to the trigger conditions of each trigger program in the system catalog table 91. If they match the trigger conditions of a trigger program, then the trigger manager 95 initiates the triggers to process the trigger event and perform the action indicated by the trigger, such as updating the time stamp or customer status, in the foregoing example.
Program 40 can be loaded into computer 10 from a computer readable media 80 such as a magnetic disk or tape, CD, DVD, etc. or downloaded from the Internet 82 via a TCP/IP adapter card 84.
Program 95 can be loaded into computer 10 from computer readable media 80 or downloaded from the Internet 82 via TCP/IP adapter card 84.
Based on the foregoing, system, method and program product for semi-automatically generating triggers have been disclosed. However, numerous modifications and substitutions can be made without deviating from the scope of the present invention. Therefore, the present invention has been disclosed by way of illustration and not limitation, and reference should be made to the following claims to determine the scope of the present invention.
