This works when using SQL Geometry as the storage type, I'm not sure you can do what you're asking when using SDE binary, unless you cook up a really complicated stored procedure. As always, ESRI will disown you if you call tech support with any SDE issue and they discover you're using triggers, SP's, and such "under the hood". This allows to either populate UTM XY on insert from a GIS client, or create the geometry object on insert from a non-gis client (using a SP that also honors the Object ID creation!). In addition, it is also performing a number of intersect operations to populate some attributes that we adminstratively in all of our data per local protocol. Finally, it's allowing us to set the GlobalID (LocationID Field) as a sequential GUID, which has done wonders to improve performance over the non-sequential default ESRI GUID. Use at your own risk!
CREATE TRIGGER [dbo].[INSERT_VM_PLOTS_Location_ID]
ON [dbo].[VM_PLOTS]
INSTEAD OF INSERT NOT FOR REPLICATION
AS BEGIN
SET NOCOUNT ON;
INSERT dbo.VM_PLOTS(
OBJECTID, VError, MapSource, SourceDate, MapMethod, QuadName, UnitCode, EditDate, UnitName, IsExtant, HError, County, State, LAT, LON, Watershed, PlaceName, TrailName, StreamName, Landform, Meta_MID, Road, Elevation, Year, Notes, Coord_Units, Coord_System, Datum, UTM_Zone, ManagementZone, ParkDistrict, EditBy, CreateBy, CreateDate, RegionCode, Restriction, Soil, Geology, HistoricDisturb, Veg, Level4EcoReg, AvgRain, MeanDailyTemp, Loc_Name, Directions, Description, Loc_Notes,X_Coord, Y_Coord, Shape
)
SELECT
a.OBJECTID, a.VError, a.MapSource, a.SourceDate, a.MapMethod, b.NAME, a.UnitCode, a.EditDate, a.UnitName, a.IsExtant, a.HError, c.NAME, d.NAME, a.LAT, a.LON, e.HU_12_Name, a.PlaceName, a.TrailName, a.StreamName, a.Landform, a.Meta_MID, a.Road, a.Elevation, a.Year, a.Notes, a.Coord_Units, a.Coord_System, a.Datum, a.UTM_Zone, g.FCSubtype, f.District, a.EditBy, a.CreateBy, a.CreateDate, a.RegionCode, a.Restriction, j.MUNAME, k.GLG_SYM, l.CLASSES, h.VitalName, p.EPA_REGION, q.RANGE, a.MeanDailyTemp, a.Loc_Name, a.Directions, a.Description, a.Loc_Notes,a.X_Coord, a.Y_Coord, a.Shape
From
(SELECT
OBJECTID, VError, MapSource, SourceDate, MapMethod, QuadName, UnitCode, EditDate, UnitName, IsExtant, HError, County, State, LAT, LON, Watershed, PlaceName, TrailName, StreamName, Landform, Meta_MID, Road, Elevation, Year, Notes, Coord_Units, Coord_System, Datum, UTM_Zone, ManagementZone, ParkDistrict, EditBy, CreateBy, CreateDate, RegionCode, Restriction, Soil, Geology, HistoricDisturb, Veg, Level4EcoReg, AvgRain, MeanDailyTemp, Loc_Name, Directions, Description, Loc_Notes,
SHAPE = CASE WHEN SHAPE IS NOT NULL
THEN SHAPE ELSE Geometry::STPointFromText('POINT('
+ CAST(X_Coord AS VARCHAR(20)) + ' '
+ CAST(Y_Coord AS VARCHAR(20)) + ')', 26917) END,
X_Coord = CASE WHEN SHAPE IS NULL THEN X_Coord ELSE SHAPE.STX END,
Y_Coord = CASE WHEN SHAPE IS NULL THEN Y_Coord ELSE SHAPE.STY END
FROM inserted)
AS a
LEFT OUTER JOIN grsm.dbo.USGS_24K_TOPOMAP_BOUNDARIES AS b
ON b.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.GRSM_COUNTIES AS c
ON c.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.GRSM_STATES AS d
ON d.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.WBD_12 AS e
ON e.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.RANGER_DISTRICTS AS f
ON f.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_VEG AS h
ON h.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.GRSM_GMP_ZONE AS g
ON g.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN iandm.DBO.GRSM_Soil_Taxonomy AS j
ON j.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.grsmglg AS k
ON k.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_DISTURBANCE_HISTORY AS l
ON l.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_LEVEL_IV_ECOREGIONS AS p
ON p.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.AVERAGERAINFALL as q
ON q.Shape.STContains(a.Shape) = 1;
end
