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
CREATE TRIGGER [dbo].[GRSM_POINTS_OF_INTEREST_Update_Geo] ON [dbo].[GRSM_POINTS_OF_INTEREST] after UPDATE NOT FOR REPLICATION AS BEGIN SET NOCOUNT ON; UPDATE p SET X_Coord = CASE WHEN i.shape.STDimension() = 2 THEN i.shape.STCentroid().STX ELSE i.shape.STEnvelope().STCentroid().STX END, Y_Coord = CASE WHEN i.shape.STDimension() = 2 THEN i.shape.STCentroid().STY ELSE i.shape.STEnvelope().STCentroid().STY END, QuadName = COALESCE(b.name, p.QuadName), Watershed = COALESCE(c.HU_12_Name, p.Watershed), County = COALESCE(d.Name, p.County), State= COALESCE(e.Name, p.State), SubRangerDistrict = COALESCE(f.District, p.SubrangerDistrict) FROM dbo.GRSM_POINTS_OF_INTEREST AS p INNER JOIN inserted AS i ON i.Location_ID = p.Location_ID LEFT OUTER JOIN grsm.dbo.USGS_24K_TOPOMAP_BOUNDARIES AS b ON b.Shape.STContains(i.Shape) = 1 LEFT OUTER JOIN grsm.dbo.WBD_12 AS c ON c.Shape.STContains(i.Shape) = 1 LEFT OUTER JOIN grsm.dbo.GRSM_COUNTIES AS d ON d.Shape.STContains(i.Shape) = 1 LEFT OUTER JOIN grsm.dbo.GRSM_States AS e ON e.Shape.STContains(i.Shape) = 1 LEFT OUTER JOIN dbo.GRSM_SUB_RANGER_DISTRICTS AS f ON f.Shape.STContains(i.Shape) = 1 WHERE EXISTS ( SELECT 1 FROM inserted AS i2 INNER JOIN deleted AS d ON i2.Location_ID = d.Location_ID WHERE i2.Location_ID = i.Location_ID AND i2.Shape.STEquals(d.Shape) = 0 -- ...and NULL handling if necessary ); END GO
Reviving an old thread but I know but I was just looking into this same thing and there are templates on how to set up a trigger if you're using MSSQL as your DBMS on MSDN: CREATE TRIGGER (Transact-SQL). It looks like the syntax is very similar across major DBs: Database trigger - Wikipedia, the free encyclopedia.
If this behavior is always triggered in an ArcMap Desktop edit session whenever you create a feature or modify its geometry, you can use Attribute Assistant to do this using the X_COORDINATE and Y_COORDINATE methods. You need to create an entry for each method and each target feature class in the dynamicValues table and add that table to your map, then make sure the Attribute Assistant add-in is turned on. You can get Attribute Assistant here by pressing the Download button.