Create a table synchronization trigger for each site for data synchronization and a data synchronization trigger

Create a table synchronization trigger for each site for data synchronization and a data synchronization trigger

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;

How does one synchronize update data during cross-database trigger table synchronization?

Why not?
1 do not understand trigger principles and working processes?
2. Do not understand that example?

If you do not understand the principles and procedures, there will be a lot of baidu. I will mention it in any book that will introduce the database and will not repeat it. This example may be a little long. I will translate it for you.
========================================
CREATE trigger [database A. tr_user] on [user]
/* Create A trigger named tr_user in the user table of database */
For update, insert, delete
/* Modify, insert, and delete monitoring data */
As
Alter table database B .. [user] disable trigger [database B. tr_user]
/* Do not trigger this table when modifying it. disable it */
-----------------------------------
If not exists (select * from deleted) -- insert
/* If the deleted table is empty, then */
Insert database B .. [user] (username, userpass, landtime) select username, password, lastlogin from inserted
/* Insert the inserted Table (that is, the newly inserted set for the trigger) to B. user */
Else if not exists (select * from inserted) -- delete
/* Otherwise, if the inserted set is empty */
Delete database B .. [user] where id in (select userid from deleted)
/* Delete the id in B. user that appears in the deleted set. in this example, in */is used */
Else -- Update
Update [user]
Set
[User]. username = I. username,
[User]. userpass = I. password
From database B .. [user] as [user],
Inserted as I
Where [user]. id = I. userid
/* Update is very obvious. If all updated files are updated, you can keep them the same */
Alter table database B .. [user] enable trigger [database B. tr_user]

Here we use a technique to determine whether the current table is inserted or deleted based on the inserted set and the Ed set. If the current table is inserted, the other table is deleted and the other table is deleted. the simplest update. (It may be used to exchange data between two tables, which is different from your purpose)

If you find it hard to understand, create three triggers separately and insert delete update, which can reduce some judgments, such as insert
You can first Delete the IDs in inserted (to prevent duplication) in bbb ccc and then insert them all.

I don't know why you want to do this. It doesn't make much sense. obvious redundancy. if you want to retain data, how nice is it to regularly add a scheduled task to use DTS... backup is also very useful.

Hope I understand that there is no error... the remaining full text>

SQL server2000 how to write a trigger to synchronize data in two tables

Create trigger Mytrg
On table1
For insert
As
Begin
Insert into tabe2 select * from inserted
End

The trigger itself works in a single way. insert one record for automatic processing once, insert N records in batches at a time, and trigger for processing N times.

How the for insert trigger works:

When data is inserted, each time a record is inserted, a trigger is triggered to save all the inserted data in the inserted temporary table,

In the preceding example, insert into tabe2 select * from inserted inserts data from the inserted temporary table into the tabe2 table,

After the trigger is executed, the system automatically deletes the inserted Table. If multiple Insert entries in the batch, the trigger will be started again.

Basic requirements: The tabe2 table has the same structure as the table1 table.