Create a table synchronization trigger for each site for Data Synchronization

As mentioned in the previous blog, when data synchronization is performed, you need to create a trigger for each operation table on each site. During addition, deletion, and modification, the task of a trigger is to concatenate the SQL statement to be operated into a string and save it in the synchro_tb_operate_log table. If an exception occurs during trigger execution, the exception information is saved in another table: SYNCHRO _

As mentioned in the previous blog, when data synchronization is performed, you need to create a trigger for each operation table on each site. During addition, deletion, and modification, the task of a trigger is to concatenate the SQL statement to be operated into a string and save it in the synchro_tb_operate_log table. If an exception occurs during trigger execution, the exception information is saved in another table: SYNCHRO _

As mentioned in the previous blog, when data synchronization is performed, you need to create a trigger for each operation table on each site. During addition, deletion, and modification, the task of a trigger is to splice the SQL statement of the operation into a string and save it in the synchro_tb_operate_log table. If an exception occurs during the trigger execution, the exception information is saved in another table: synchro_data_pai_log.

Synchro_tb_operate_log field information: primary key ID, spliced SQL statement (including primary key ID and region code), whether synchronization is completed (0 by default), Creation Time

Synchro_data_pai_log field information: primary key ID, trigger Exception name, trigger exception information, trigger exception occurrence time

The following is the code for creating item_rec. You can also learn the syntax and knowledge about creating a trigger:

Create or replaceTRIGGER "ADMIN ". "TRIGGER_ITEM_REC" after insert or update or delete on ITEM_REC for each row/** HEAD * @ name project record table trigger * @ description change event, insert ddl statements into the synchronization table * @ version 1.0.0 * @ author Tang * @ create-date 2014-07-01 */declare v_ SQL nvarchar2 (3000); -- temporarily store SQL v_zdcode varchar2 (30 ): = '20140901'; -- site code v_exception varchar2 (08052); begin v_ SQL: = null; case when inserting then -- insert data v_ SQL: = 'insert into ITEM_REC ('|' ID, PROTEAMNAME, PY, PROID, '| 'jctype, WORKFLAG, ZXFLAG, JWDCODE) '| 'values (''' |: new. ID | ''', ''' | replace (: new. PROTEAMNAME, ''', '') | ''', ''' |: new. PY | ''', ''' |: new. PROID | ''', ''' |: new. JCTYPE | ''', ''' |: new. WORKFLAG | ''', ''' |: new. ZXFLAG | ''', ''' | v_jwdcode | ''') '; when updating then -- Update Data v_ SQL: = 'Update ITEM_REC set' | 'proteamname = ''' | replace (: new. PROTEAMNAME, ''', '') | ''' | ', PY = ''' |: new. PY | ''' | ', PROID = ''' |: new. PROID | ''' | ', JCTYPE = ''' |: new. JCTYPE | ''' | ', WORKFLAG = ''' |: new. WORKFLAG | ''' | ', ZXFLAG = ''' |: new. ZXFLAG | ''' | 'where' | 'id = '|: old. ID | 'and JWDCODE = ''' | v_jwdcode | '''; when deleting then -- delete data v_ SQL: = 'delete from ITEM_REC t where t. ID = '|: old. ID | 'and t. JWDCODE = ''' | v_jwdcode | '''; end case; if v_ SQL is not null then -- if v_ SQL is not empty, insert the corresponding information into the values table insert into values (sd_record_id, sd_record_ SQL, sd_flag, create_time) values (values, v_ SQL, 0, to_char (sysdate, 'yyyy-MM-DD HH24: MI: SS '); end if; exception when others then -- if an exception occurs during execution, insert the exception information into the synchro_data_pai_log table v_exception: = substr (sqlcode | '---' | sqlerrm, 1,500); insert into synchro_data_1__log (ID, TRIGGER_NAME, EXCEPTION_INFO, EXCEPTION_TIME) values (sys_guid (), 'trigger _ dict_proteam ', v_exception, to_char (sysdate, 'yyyy-MM-DD HH24: MI: ss'); end TRIGGER_ITEM_REC;