Data synchronization between SQL Server and Oracle (trigger trigger)

Source: Internet
Author: User
Tags getdate

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.