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