Two-way synchronization of some fields in the Oracle database table using packages and triggers

Requirement: the database contains two tables with different structures. Partial fields of the two tables need to be synchronized in real time.

Implementation solution: Create a trigger for synchronization on the two tables respectively. However, this operation may cause a problem and trigger to be triggered cyclically. Therefore, you need to make a judgment before the trigger is triggered. If the operation is triggered by the trigger, no operation is performed. Otherwise, the operation defined by the trigger is executed. To implement this, I began to consider temporarily disabling trigger B in trigger a, but found that alter trigger disable is illegal. Then someone mentioned adding a field in the table, it is used for identification, but this requires modifying the table structure. Although it can meet the requirements, it is not very good, so it is not used. Later, I learned about the features of the package by studying Oracle Objects, therefore, we decided to use the package method for implementation and use the package variables for identification.

/* During the operation, you must execute the 'package ', 'function', and 'trigger' respectively. After performing one-time execution, check the 'package 'and 'function ', whether the 'trigger 'status is valid */-- create a Baotou create or replace package pk_check_active is -- identify whether the trigger triggers N number: = 0;
-- Obtain the trigger ID of a trigger. 1 indicates the trigger function getactive return number;
-- Set the status procedure setactive (N1 in number); End pk_check_active;
/* Here/must not be missing * // create or replace package body pk_check_active as function getactive return number is begin return N; end getactive; Procedure setactive (N1 in number) is begin n: = N1; end setactive; end pk_check_active; -- obtains the guid in the format of create or replace function creategs_oid return varchar2 is guid varchar (64 ); result varchar (64); begin guid: = sys_guid (); Result: = substr (guid, 1, 8) | '-' | substr (guid, 9, 4) | '-' | substr (guid ); return (result); End creategs_oid; -- trigger tableacreate or replace trigger tr_cs_user after insert or update or deleteon tablea for each rowbegin if exist () = 1 then pk_check_active.setactive (2); return; else pk_check_active.setactive (1); end if; If inserting then insert into tableb (gs_oid, s_username, s_password) values (creategs_oid (),: New. name,: New. pass); elsif updating then update taableb t set T. s_username =: New. name, T. s_password =: New. pass where T. s_username =: Old. name; elsif deleting then delete from tableb t where T. s_username =: Old. name; end if; pk_check_active.setactive (0); end; -- get the maximum value of the specified column + 1 (also an ID identification method, which is not made into an auto-increment field at the beginning, therefore, when writing a trigger, you must implement it by yourself.) Create or replace function getcsuserid return numberis result number; begin select max (userid) + 1 into result from tablea; Return (result ); end getcsuserid; -- triggers tablebcreate or replace trigger tr_bs_user after insert or update or deleteon tableb for each row begin if pk_check_active.getactive () = 1 then pk_check_active.setactive (2); return; else pk_check_active.setactive (1); end if; If inserting then insert into tablea (userid, name, pass, version, useable, remark) values (getcsuserid (),: New. s_username,: New. s_password, 'sde. default ', '0', 'user automatically add'); elsif updating then update tablea t set T. name =: New. s_username, T. pass =: New. s_password where T. name =: Old. s_username; elsif deleting then delete from tablea t where T. name =: Old. s_username; end if; pk_check_active.setactive (0); end;

The above Code involves packages, triggers, functions, and stored procedures. This example shows how to use common triggers and how to format guids. In this way, only tables under the same user can be synchronized. This method is invalid if it is cross-user.

Reference "because the global variables of package are invisible at the database level, each session can be considered as an instantiated package object. The value assignment operation on global variables at the session level is not seen by other sessions, which reflects data encapsulation. "Therefore, the control through the package variables must be in a session. According to my understanding, if ADO is used, a connection is considered a session. Is there any cross-Session access method? I asked Google if there is a solution, but I won't sell it if I fail to test it. I will mention it in reference articles.

 

Reference: http://www.2cto.com/database/201203/123813.html

Http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html

Http://blog.chinaunix.net/uid-186431-id-84823.html

Http://www.cnblogs.com/tukzer/archive/2010/12/08/1900047.html