The following assumes you have a unique ID field (SQL, GUID in SDE), that in SQL is set to "newsequentialid()". Lets assume the field is called "LocationID". Note how that column is NOT INCLUDED in this trigger! In the Adds table, it is a NON-SEQUENTIAL ID, but when it hits the business table, it becomes sequential. The rest of the trigger deals with using SQL geometry operations to autopopulate some columns. This will ONLY work on data that is versioned with move edits to base checked, which kills any replication plans you have. If you can't move edits to base, you'll need to wrap this up in a stored proc using a cursor, which IMHO was more work than it's worth.
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
I take it one step further and get this all going in SQL/SDE, without declaring a globalID in Arc Cat, then change the object flags on the "LocationID" column in sde_column_registry to "256", thus giving myself a sequential globalid. The difference in performance is immediate and drastically improved.
