SQL update/insert triggers causing RuntimeError: workspace already in transaction mode

[VincentLantaca]

I have the following trigger on both update and insert for a table called blocks which stores polygons:

AFTER INSERT AS BEGIN
	SET NOCOUNT ON

	DECLARE  int, @ranch_num int, @block_treatment nvarchar(256), @shp geography, @copied_from int, @gdb_from_date datetime2(7)

	SELECT * INTO #Temp
	FROM inserted
	
	WHILE EXISTS (SELECT * FROM #TEMP) BEGIN
		SELECT TOP 1  = OBJECTID, @ranch_num = TRANSACTIONAL_RANCH_NUMBER, @block_treatment = TREATMENT_TYPE, @shp = SHAPE, @copied_from = CAST(COPIED_FROM as int), @gdb_from_date = GDB_FROM_DATE
		FROM #Temp

		IF @ranch_num IS NOT NULL
			BEGIN
				IF @block_treatment = 'ORIGINAL'
					BEGIN
						UPDATE a
						SET a.ORIGIN_BLOCK = a.OBJECTID
						FROM BLOCKS a
						WHERE a.OBJECTID = 
					END
				ELSE
					BEGIN
						IF (SELECT TOP(1) a.OBJECTID
							FROM DEV_ENV.aws.BLOCKS a LEFT JOIN  DEV_ENV.aws.TRANSACTIONAL_RANCH_NUMBERS t on a.TRANSACTIONAL_RANCH_NUMBER = t.TRANSACTIONAL_RANCH_NUMBER
							WHERE a.SHAPE.STIntersects(
								geography::STGeomFromText(
									geometry::Point(
										geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STX,
										geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STY,
										4326
									).STAsText()
								, 4326)
							)=1 AND a.TREATMENT_TYPE = 'ORIGINAL' AND t.MEDIA = 1 AND a.TRANSACTIONAL_RANCH_NUMBER = @ranch_num
							AND a.GDB_TO_DATE = '9999-12-31 23:59:59.0000000' AND a.ORIGIN_BLOCK IS NOT NULL
							ORDER BY a.GDB_FROM_DATE desc
						) IS NOT NULL
							BEGIN
								UPDATE BLOCKS
								SET
									ORIGIN_BLOCK = (
										SELECT TOP(1) a.OBJECTID
										FROM DEV_ENV.aws.BLOCKS a LEFT JOIN  DEV_ENV.aws.TRANSACTIONAL_RANCH_NUMBERS t on a.TRANSACTIONAL_RANCH_NUMBER = t.TRANSACTIONAL_RANCH_NUMBER
										WHERE a.SHAPE.STIntersects(
											geography::STGeomFromText(
												geometry::Point(
													geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STX,
													geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STY,
													4326
												).STAsText()
											, 4326)
										)=1 AND a.TREATMENT_TYPE = 'ORIGINAL' AND t.MEDIA = 1 AND a.TRANSACTIONAL_RANCH_NUMBER = @ranch_num
										AND a.GDB_TO_DATE = '9999-12-31 23:59:59.0000000' AND a.ORIGIN_BLOCK IS NOT NULL
										ORDER BY a.GDB_FROM_DATE desc
									)
								WHERE OBJECTID =  
							END
					END
			END
		ELSE
			BEGIN
				UPDATE a
				SET a.ORIGIN_BLOCK = null
				FROM BLOCKS a
				WHERE a.OBJECTID = 
			END

		DELETE FROM #Temp WHERE OBJECTID = 
	END
END

Essentially, what I am trying to do is if a block that is being inserted/updated has a non-original treatment type, I need to find the most recent ORIGINAL block that contains the centroid of the block being inserted/updated.

To do this in a trigger, I am first creating a Point geometry to find the centroid, and then converting that geometry to a geography which is used as input for SHAPE.STIntersects. However the inputs to SHAPE.STIntersects is causing an issue when I run geoprocessing scripts that will either call UpdateCursor or InsertCursor on the blocks table. The error I get is:

RuntimeError: workspace already in transaction mode

If I were to change the input of SHAPE.STIntersects:

WHERE a.SHAPE.STIntersects(
	geography::STGeomFromText(
		geometry::Point(
			geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STX,
			geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STY,
			4326
		).STAsText()
	, 4326)
)=1

to use the EnvelopCenter of the shape instead of a Point geometry of the centroid:

WHERE a.SHAPE.STIntersects(@shp.EnvelopeCenter())=1

I will no longer get the RuntimeError, however it isn't as accurate as using the centroid.

I am not so familiar working with shapes in SQL, any ideas what could be wrong with using the centroid Point geometry?