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

VincentLantaca — Tue, 31 Jan 2023 00:05:02 GMT

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?

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

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