CN1945579A - Technology for realizing data synchronization between Oracle data base lists by using PL/SQL trigger - Google Patents

Abstract

The invention relates to a technology which is used in the human resources management and materials management software, and uses PL/SQL trigger to realize data synchronization between the tables of Oracle Spatial. It characters are that: it establishes the temporary table interimtable, uses the function before and after of Delete trigger, storing the Id number in the interimtable and reading it out into the data variables to avoid ':old.Id' direct application, to realize data synchronization between the tables of Oracle Spatial and achieve the purpose of inquiring information accurately and conveniently.

Description

Use the PL/SQL trigger and realize data synchronization technology between the oracle database table

Technical field:

The present invention relates to a kind of technical method that is used for software application such as human resource management, material management.

Background technology:

Along with the development of infotech, the oracle database technology promotes the use of every field more and more widely, in human resource management, material management software.Use oracle database and carry out in the process of data processing, in order to explain the different attribute of a certain affairs, the designer sets up N transaction attributes table according to the different attribute of affairs, and attribute list is used to write down the evolution process of transaction attributes.N attribute list links up with transaction table with one or more key word.In order to satisfy user's demand, make things convenient for the realization of polling routine, the designer often joins together the field of N transaction attributes table, sets up the community set table, when attribute list changes, the latest development of community set table record attribute list.At present, in the different table of human resource management software oracle database, set up Update, Insert trigger, follow the PL/SQL language rule, all can use ": new.Id ", ": old.Id " and explain the setting of compound condition value in the Where condition, and then realize the new extraction that is worth, and the data in the community set table are upgraded.But in the implementation procedure of Delete trigger, " old.Id " can not be cited, if quoted ": old.Id ", when the Delete trigger moves, the recurrence error message can be pointed out by the oracle database system, and the function that this mistake provides with Oracle itself can't be resolved.

Summary of the invention:

The object of the present invention is to provide and a kind ofly can realize data sync between the oracle database table and can avoid the application PL/SQL trigger of recurrence error message to realize data synchronization technology between the oracle database table.

The technical solution adopted for the present invention to solve the technical problems is: this technology is by setting up temporary table interimtable, use the before and the after function of Delete trigger, by among the interimtable to Id number storage, and read in the data variable, avoidance is to the direct application of ": old.Id ", realize data sync between the oracle database table, reach the purpose of the accurate information of convenient inquiry.With the human resource management software is that the example implementation step is as follows:

1, sets up the transaction table table1 that is writing down worker's natural situation, comprise Id, name (Name), sex (Sex), national (Nationality) etc. in the table, wherein Id (key word) is this table major key, Varthar2 is a character string type, length refers to byte, 1 byte of English 1 letter, 1 Chinese character of Chinese character is 2 syllables;

Table 1 transaction table table1

The row name	Type	Length
			Id	Varchar2	10
Name	Varchar2	10
			Sex	Varchar2	2
Nationality	Varchar2	6

2, set up the transaction attributes table table2 that is writing down worker's educational background differentiation attribute, comprise Id, graduation time (Graduationdate), previous graduate college (School) etc. in the table, wherein Id is the external bond of this table, and Id, Gnaduationdate are the index keys of this table;

First transaction attributes table of table 2 table2

The row name	Type	Length
			Id	Varchar2	10
Graduationdate	Datetime
			School	Varchar2	30

3, set up second the transaction attributes table table3 that is writing down worker's resume, comprise Id, zero-time, work unit etc. in the table, wherein Id is the external bond of this table, and Id, Startdate are the index keys of this table;

Second transaction attributes table of table 3 table3

The row name	Type	Length
			Id	Varchar2	10
Startdate	Datetime
			Workunit	Varchar2	30

4, establish the community set table table0 of table1, table2, three table set of table3, wherein, Id is the external bond of this table, and the record in the table forms automatically;

Table 4 community set table table0

The row name	Type	Length
			Id	Varchar2	10
Name	Varchar2	10
			Sex	Varchar2	2
Nationality	Varchar2	6
			Graduationdate	Datetime
School	Varchar2	30
			Startdate	Datetime
Workunit	Varchar2	30

5, in different tables, set up Update, Delete, Insert trigger, use the PL/SQL language rule, realize the interlock of table0 table content.

Technical method provided by the invention is easy and simple to handle, and is easy to implement, utilizes it can realize making things convenient for data sync between management software oracle database tables such as human resources the carrying out of polling routine, and can avoid the recurrence error message.

Description of drawings: table 1, table 2, table 3, table 4 are respectively needs the transaction table table1, transaction attributes table table2, table3 and the community set table table0 that set up in the invention process process.

Embodiment:

According to aforesaid technical solution of the present invention implementation step and the chart that provides, corresponding concrete literal of input or numeral can be carried out inquiry and the application of human resource management software oracle database to program smoothly.

If material management or other management softwares then need be set up correspondent transaction table table1, transaction attributes table table2, table3 and community set table table0, and import corresponding data information and get final product.

Claims (1)

1, a kind of application PL/SQL trigger is realized data synchronization technology between the oracle database table, it is characterized in that: this method is by setting up temporary table interimtable, use the before and the after function of Delete trigger, by among the interimtable to Id number storage, and read in the data variable, avoidance realizes data sync between the oracle database table to the direct application of ": old.Id ", reaches the purpose of the accurate information of convenient inquiry; Implementation step is as follows:

(1), set up the transaction table table1 writing down worker's natural situation, comprise Id, name, sex, nationality etc. in the table, wherein Id is this table major key;

(2), set up and to write down the transaction attributes table table2 that worker's educational background develops attribute, comprise Id, graduation time, previous graduate college etc. in the table, wherein Id is the external bond of this table, Id, Gnaduationdate are the index keys of this table;

(3), set up second transaction attributes table table3 writing down worker's resume, comprise Id, zero-time, work unit etc. in the table, wherein Id is the external bond of this table, Id, Startdate are the index keys of this table;

(4), establish the community set table table0 of three table set of table1, table2, table3, wherein, Id is the external bond of this table, the record in the table forms automatically;

(5), in different tables, set up Update, Delete, Insert trigger, application PL/SQL language rule, the interlock of realization table0 table content.