Good afternoon,
I'm creating a trigger to be present on 3 databases, one of which is on a linkedserver. When inserting data in one of the databases, the objective is to replicate the information in the remaining databases.
Whenever I do an insert, I get the error 'Transaction context in use by another session.'
Anyone knows why this is happening? And is there a way to surprass this?
DROP TRIGGER Insertdata
USE A
GO
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER Insertdata ON DATA WITH ENCRYPTION FOR INSERT AS
SET NOCOUNT ON
if(Upper((SELECT DB_NAME()))=UPPER('A')) Begin
----------------------------------------------------------------------------------------------
--------------------------------------------- Insert Data ------------------------------------
----------------------------------------------------------------------------------------------
--Create variable for database name and query variable
DECLARE @DB_Name VARCHAR(100) -- database name
DECLARE @Local VARCHAR(1) -- database name
DECLARE @query VARCHAR(4000) -- query variable
--Declare the cursor
DECLARE db_cursor CURSOR LOCAL FOR
-- Populate the cursor with the selected database name
select t.name,t.islocal from (
Select name, '1' as islocal from master.sys.databases WHERE name in ('A','B','C')
union all
select name, '0' as islocal from LinkedServer.master.sys.databases WHERE name in ('A','B','C')
)t
OPEN db_cursor
--Moves the cursor to the first point and put that in variable name
FETCH NEXT FROM db_cursor INTO @DB_Name,@Local;
-- while loop to go through all the DB selected
WHILE @@FETCH_STATUS = 0
BEGIN
if(@Local ='0') begin
Set @DB_Name = 'LinkedServer.' + @DB_Name
end
-- Check if received data exists
SET @query = N'Select id from ' + @DB_Name +'.dbo.data where data.id='''+(Select inserted.id from inserted)+''''
EXEC(@query)
--If doesnt exists, then insert data
if @@ROWCOUNT < 1 begin
SET @query = N'INSERT INTO ' + @DB_Name +'.dbo.data(id,name,surname)
values('+''''+(select inserted.id from inserted)+''''
+','+''''+(select convert(varchar,inserted.name) from inserted)+''''
+','+''''+(select convert(varchar,inserted.surname) from inserted)+''''
+')'
EXEC (@query)
--Fetch the next record from the cursor
end
FETCH NEXT FROM db_cursor INTO @DB_Name,@Local
END
--Close and deallocate cursor
CLOSE db_cursor
DEALLOCATE db_cursor
End
GO
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
SET QUOTED_IDENTIFIER OFF
GO
EDIT: I think the problem here is the fact, that my trigger is being executed on different databases. For example, the current database is A, so the trigger should execute on B and C, but I realized that when A calls B, B executes the trigger aswell. C doesn't executes because gives the error on B. Thought db_name() would fix this, but guess it doesn't
