Sql-server – insert trigger in sql server to linked oracle

linked-serveroraclesql server

i have created a linked server to oracle in sql server and i want to write one trigger on table in sql server to insert the data into oracle table. For example the table a is in sql server and table b is on oracle. If i insert record in table a the values needs to be copied or inserted into table b of Oracle.
both the tables have same structure

table a
id varchar2(10);
name varchar2(10);

table b
id varchar2(10);
name varchar2(10);

Best Answer

The answer is a "simple" trigger as I've outlined below for you. I didn't have enough information to name it properly, but hopefully you will be able to fill in the gaps. This will fire AFTER the insert operation on the SQL side, then insert the relevant records to the Oracle linked server (assuming it's setup correctly).

However, a few words of advice from someone whose environment does this kind of thing a lot (using triggers to shove data from A to B).

Depending on what kind of client is accessing this, it will escalate to a distributed transaction, which may or may not work for all clients. If it doesn't work then you will need to change this to using a cursor (or find some way to make distributed transactions work for your environment). But don't test with just management studio, use the application(s) in your environment as well.

There is no way to catch an error in here (not even in a try/catch block). If the insert into Oracle fails, then the transaction that wrote the data to TableA will also fail. This may or may not be what you want to happen, but it is what will happen and there is nothing you can do to stop it.

Finally, if a delay is acceptable and you only need it on INSERTED data, then might I suggest using an agent job to run periodically, query the Oracle side for the highest value id, then send over everything in TableA that is greater than that id.

CREATE OR ALTER TRIGGER trg_TableA ON dbo.TableA
AFTER INSERT
AS
BEGIN

    INSERT INTO OracleLinkedServerName.SchemaName.TableB
        (id, [name])
    SELECT I.id, I.[name]
    FROM INSERTED AS I


END
Related Question