Data synchronization between SQL Server and Oracle (trigger trigger)

Speaking of synchronization, in fact, "homework" schedule stored procedures to operate the data, add, delete, change, all in the inside, combined with triggers, cursors to achieve, about job scheduling, using 5 seconds to run a "second-level operation", so basic even faster "synchronization"

Do is SQL Server to the Oracle side synchronization, first set up on the SQL Server to the Oracle side of the linked server, I use a view "encapsulated" a table under the linked server.

CREATE VIEW V_ora_publishlastrec As SELECT * from OracleDB. Roadsms. Publishlastrec OracleDB linked server name, roadsms as table space name, Publishlastrec as data table name

The insert,delete,update triggers are then established on the tables that SQL Server will synchronize, respectively.

The script is as follows:

---Description: Modiid equals 1 for insert,2 to delete,3 for update Create trigger Trg_publishlastrec_insert on PUBLISHLASTREC for insert As INSERT INTO dbo. Publishlastrec_sql (modiid,signalguid,areano,signalnote,areanote,publishroadstatus,publishtime,isexec) Select ' 1 ', Signalguid,areano,signalnote,areanote,publishroadstatus,publishtime, ' 0 ' from inserted Create trigger Trg_publishlastrec_update on PUBLISHLASTREC for update As INSERT INTO dbo. Publishlastrec_sql (modiid,signalguid,areano,signalnote,areanote,publishroadstatus,publishtime,isexec) Select ' 3 ', Signalguid,areano,signalnote,areanote,publishroadstatus,publishtime, ' 0 ' from inserted Create trigger Trg_publishlastrec_delete on Publishlastrec for delete As INSERT INTO dbo. Publishlastrec_sql (modiid,signalguid,areano,signalnote,areanote,publishroadstatus,publishtime,isexec) Select ' 2 ', Signalguid,areano,signalnote,areanote,publishroadstatus,publishtime, ' 0 ' from deleted

All of the operations are to insert data from each table into a table, the above uniformly inserted table is publishlastrec_sql, record the operation of the identity, to identify whether the record is inserted, deleted, or modified, MODIID equals 1 for the delete, insert,2 3 is update, field Isexec identifies whether the record has been processed, 0 is not executed, and 1 is executed

Then there is the most critical step, the stored procedure

The script is as follows:

ALTER proc Pro_publishlastrec_sql As DECLARE @modiid int DECLARE @signalguid int Declare @areano numeric (1,0) DECLARE @signalnote varchar (50) DECLARE @areanote varchar (50) DECLARE @publishroadstatus varchar (20) DECLARE @publishtime varchar (50) If not EXISTS (SELECT * from Publishlastrec_sql where isexec=0) Begin TRUNCATE TABLE Publishlastrec_sql Return End DECLARE cur_sql cursor FOR Select Modiid,signalguid,areano,signalnote,areanote,publishroadstatus,publishtime from Publishlastrec_sql where isexec=0 order by [Id]--isexec 0 is not executed, 1 is executed Open Cur_sql FETCH NEXT from Cur_sql to @modiid, @SignalGUID, @AreaNo, @SignalNote, @AreaNote, @PublishRoadStatus, @PublishTime While @ @fetch_status =0 Begin if (@modiid =1)--insert Begin Insert into V_ora_publishlastrec (signalguid,areano,signalnote,areanote,publishroadstatus,publishtime) VALUES (@SignalGUID, @AreaNo, @SignalNote, @AreaNote, @PublishRoadStatus, @PublishTime) End if (@modiid =2)--delete Begin Delete from V_ora_publishlastrec where [email protected] and [email protected] End if (@modiid =3)--Modify Begin Update V_ora_publishlastrec Set [email protected],[email protected],[email protected], [Email protected] where [email protected] and [email protected] End Update Publishlastrec_sql Set Isexec=1 Where CURRENT of Cur_sql FETCH NEXT from Cur_sql to @modiid, @SignalGUID, @AreaNo, @SignalNote, @AreaNote, @PublishRoadStatus, @PublishTime End Deallocate Cur_sql

The stored procedure uses a cursor to fetch the PUBLISHLASTREC_SQL record line by row, according to Modiid to determine the different data operations, the record is processed after the Isexec field is updated to 1.

Finally, the job that calls the stored procedure, builds a job that runs once a minute, and then writes in the script for "steps":

DECLARE @dt datetime SET @dt = DATEADD (minute,-1, GETDATE ()) While @dt < GETDATE () BEGIN EXEC Pro_publishlastrec_sql--here pro_publishlastrec_sql for the stored procedure you want the job to execute WAITFOR DELAY ' 00:00:05 '-wait 5 seconds, set it according to your needs END

Now we can execute the stored procedure in 5 seconds to achieve 5 seconds of data synchronization.

Data synchronization between SQL Server and Oracle (trigger trigger)