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.

Package pk_check_active n: -- get the trigger ID of the trigger, 1. Trigger getactive for trigger -- set the status setactive (n1/* Here/must not be missing */package body pk_check_active getactive setactive (n1) Creategs_oid (substr (guid ,,) substr (guid,),) substr (guid,) substr (guid, tr_cs_user after tableA pk_check_active.getactive (); inserting tableB (gs_oid, s_username, s_password) taableB t. s_username: new. name, t. s_password: new. pass t. s_username tableB t. s_username GetCSUserID (userid) result tr_bs_user after tableB pk_check_active.getactive (); inserting (GetCSUserID (),: new. s_username,: new. s_password, tableA t. name: new. s_username, t. pass: new. s_password t. name tableA t. name;

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.bkjia.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