I created trigger in SQL Server for Oracle, if new product is added in SQL Server then it should be automatically added to Oracle Table also using Linked Server. I've tried to insert record using regular query and it works but it doesn't work with trigger. Please help me figure out what's wrong. Here are my queries.
Regular query (It works)
INSERT INTO PRODUCT_IMP..PROD_MASTER.PRODUCT_TBL --ORACLE
SELECT BARCODE,NAME,SUPPLIER,CATEGORY,GETDATE() --SQL Server
FROM [dbo].[product_tbl]
Trigger (It doesn't work)
CREATE TRIGGER [dbo].[Product_INSERT]
ON [dbo].[product_tbl]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO PRODUCT_IMP..PROD_MASTER.PRODUCT_TBL
SELECT BARCODE,NAME,SUPPLIER,CATEGORY,GETDATE()
FROM
inserted
END
Gives error:
OLE DB provider "OraOLEDB.Oracle" for linked server "PRODUCT_IMP" returned message "Unable to enlist in the transaction.". Msg 7391, Level 16, State 2, Procedure Product_INSERT, Line 10 [Batch Start Line 0] The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "PROD_MASTER" was unable to begin a distributed transaction.
