Update trigger for SQL Server linked server

Question

Update trigger doesn't work while using SQL Server linked server.

I created a linked server on my local SQL Server. I can query this linked server (SELECT, UPDATE, INSERT etc.) from my local machine.

I create an UPDATE trigger one of my local tables:

CREATE TRIGGER [dbo].[update_trg]
ON [myLocalDB].[dbo].[localTable]
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE [192.168.3.102].[myRemoteDB].[dbo].[remoteTable] 
    SET lastTime = (SELECT lastTime FROM myLocalDB.dbo.localTable)    
    /* I wrote this way because lastTime = myLocalDB.dbo.localTable.lastTime didn't work */
    WHERE id = 1
END

But this trigger didn't work despite I can query my linked server database as I mentioned.

I get an error:

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "192.168.3.102" was unable to begin a distributed transaction.

I have enabled "Distributed Transaction Coordinator" and checked security settings of Local DTC properties on both machines.

This time my UPDATE query on local machine keeps executing query... and doesn't return any message or error.

Please help if anyone has faced this issue before (and solved it).

Answer 1

A few weeks later I started researching the problem again. I think I found the answer. So, I created MS DTC rule in Windows Firewall settings.

• In Windows Firewall with Advanced Security, select Inbound Rules and click New Rule in the action pane.
• On the Rule Type page click Predefined, click Distributed Transaction Coordinator, and click Next.
• On the Predefined Rules page, select all rules for Distributed Transaction Coordinator (RPC-EPMAP), Distributed Transaction Coordinator (RPC), Distributed Transaction Coordinator (TCP-In), and click Next.
• On the Action page, select Allow the connection, and click Finish.

Then my Update Trigger worked.