Insert Trigger to a Linked Server error

Question

I created an insert trigger in a table that will fire when an insert/update/delete is made. The trigger is supposed to send information about the query that fired it, but every time it fires, the error below occurs.

OLE DB provider "SQLNCLI11" for linked server "TMP-DBA-DB" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure AuditlDealerDebitAuditTrailTEST, Line 22
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "TMP-DBA-DB" was unable to begin a distributed transaction.

Here's my trigger query:

    create trigger dbo.AuditlDealerDebitAuditTrailTEST
on [dbo].[lDealerDebitAuditTrailTEST] 

after UPDATE, INSERT, DELETE
as 

declare @TableName varchar(20), @User varchar(20), @PCNo varchar(20), @server nvarchar(30), @Database varchar(30), 
        @table varchar(30),@activity varchar(20), @date datetime;

begin
         set  @TableName = 'lDealerDebitAuditTrailTEST' 

if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
    SET @User = SYSTEM_USER;
    set @PCNo = HOST_NAME();
    set @server = @@SERVERNAME;
    set @Database = DB_NAME();
    Set @table = @TableName;
    SET @activity = 'UPDATE';
    set @date = GETDATE();
      INSERT into [TMP-DBA-DB].[Josel_TestDB].[dbo].[audit](ExecutedBy,  PCNo, "Server", "Database", "Table", Activity, ExecutedOn)
         values (@User, @PCNo,  @server, @Database,  @table, @Activity, @date);
end

If exists (Select * from inserted) and not exists(Select * from deleted)
begin
    SET @Activity = 'INSERT';
    SET @User = SYSTEM_USER;
    set @PCNo = HOST_NAME();
    set @server = @@SERVERNAME;
    set @Database = DB_NAME();
    Set @table = @TableName;
    set @date = GETDATE();
     INSERT into [TMP-DBA-DB].[Josel_TestDB].dbo.audit(ExecutedBy,  PCNo, "Server", "Database", "Table", Activity, ExecutedOn)
         values (@User, @PCNo,  @server, @Database,  @table, @Activity, @date);
    end
If exists(select * from deleted) and not exists(Select * from inserted)
begin 
    SET @activity = 'DELETE';
    SET @User = SYSTEM_USER;
    set @PCNo = HOST_NAME();
    set @server = @@SERVERNAME;
    set @Database = DB_NAME();
    Set @table = @TableName;
    set @date = GETDATE();
     INSERT into [TMP-DBA-DB].[Josel_TestDB].dbo.audit(ExecutedBy,  PCNo, "Server", "Database", "Table", Activity, ExecutedOn)
         values (@User, @PCNo,  @server, @Database,  @table, @Activity, @date);
    end

    end
GO

Answer 1

Have you tried setting up the distributor transaction coordinator?

Distributed transactions are transactions that require SQL Server to get data from a source outside of the instance of SQL Server you are using (this most frequently entails the use of Linked Servers et al.

An example of a distributed transaction would be for example:

INSERT MyTable (Col1, Col2, Col3....) 
SELECT Col1, Col2, Col3 ... FROM OPENQUERY(MyLinkedServer, 'SELECT Col1, Col2, Col3 ...
FROM RemoteTable
WHERE predicate1 = Condition1')
INNER JOIN Table2 ON Table2.ColX = Col1

to set up the distributed transaction coordinator follow the steps below:

Now you click apply, it will ask for restarting the service and you say yes.

this article is also worth reading:

Do I need DTC for my SQL Server?