I'm trying to get a pair of spatially enabled tables to update one another without creating a recursive loop. I have triggers on both the local and the remote server that fire when there's an update, insert, or delete because I need each action handled slightly differently.
So far, I've tried this....
begin try
drop trigger sde.PolygonDeleteTrigger
end try
Begin catch
end catch
GO
CREATE TRIGGER sde.PolygonDeleteTrigger
ON sde.Polygons
AFTER DELETE
AS
BEGIN
declare @GUID nvarchar(max), -- GUGUID
@sqlString nvarchar(max)
set @GUID = (select GUID from deleted )
insert into openquery(remoteServer,'select GUID,TimeStamp from myDB.dbo._PolygonTriggerStatus')
values(@GUID,getDate())
if (select count(GUID) from sde._PolygonTriggerStatus where GUID = @GUID) = 0
BEGIN
set @sqlString = 'delete from openquery(remoteServer, ''select GUID from myDB.dbo.Polygons where GUID = ''''' + @GUID + ''''' '' )'
print @sqlString
execute sp_sqlexec @sqlString
END
set @sqlString = 'delete from openquery(remoteServer,''select GUID,TimeStamp from myDB.dbo._PolygonTriggerStatus where GUID = ''''' + @GUID + ''''' '' )'
print @sqlString
execute sp_sqlexec @sqlString
END
GO
The result is that I get the error message: Transaction context in use by another session.
I thought that adding the trigger status table would alleviate the issue by preventing the insert, update, and delete queries from altering the records when the GUID (GlobalID) of the record in question is present. I still get the message that "Transaction context in use by another session."
So far, I haven't found any good way to prevent the trigger on a remote server from firing.
Any thoughts?
SQL Server: 2008 Express r2