Insert Trigger to a Linked Server error

by Roxzurafa   Last Updated October 01, 2018 02:06 AM

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


Related Questions



How to determine if insert or update

Updated January 08, 2018 19:06 PM

PL/SQL Insert Update Before Trigger

Updated October 17, 2017 07:06 AM


INSERT and UPDATE in Trigger

Updated April 11, 2016 08:02 AM