Database Triggers: GDB_ARCHIVE_OIDS out of sync with regular OIDs

Question

I have a database trigger on a feature class where if a new feature is inserted, it will also copy that feature into a different feature class. Archiving is enabled on both, so there are GDB_ARCHIVE_OIDs (here forth GAO) associated with each record in the feature classes. The first feature class is smaller than the one it is copying to, so the OID (which becomes the GAO) is a lower number than the feature class it is being copied to. When I attempt to copy the feature to the larger feature class, the GAO prevents the feature being inserted because it's GAO is already in the second feature classes GAOs. How can I find the largest GAO in the second feature class to alter my insert statement to make sure I can insert the feature into the larger table? I was thinking that anytime a feature is inserted into the table, a GAO would automatically be generated so I wouldn't have to include the GAO in my SQL INSERT statement, but that does not appear to be true when using a database trigger to do this. It errors out saying the GAO field can't be null. Database trigger: USE [FocalAreasTest1] GO /****** Object: Trigger [dbo].[copy_point] Script Date: 2/24/2020 1:40:21 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[copy_point] ON [FocalAreasTest1].[dbo].[MONITORINGPOINT_1] AFTER INSERT, UPDATE NOT FOR REPLICATION AS DECLARE @MPID uniqueidentifier, @State nvarchar(2), @Point nvarchar(100), @StateObjectID int, @Route nvarchar(10), @x_coord numeric(38,8), @y_coord numeric(38,8), @notes nvarchar(150), @RouteOrder smallint, @Spring smallint, @Fall smallint, @DateAdded datetime2(7), @DateModified datetime2(7), @Archive int, @FRID uniqueidentifier, @long numeric(11,8), @lat numeric(11,8), @global uniqueidentifier, @SHAPE geometry, @GDB varbinary(max), @from datetime2(7), @to datetime2(7), @OID int, @StatePoint nvarchar(50) IF EXISTS (Select * FROM DELETED) BEGIN --Do update stuff here END ELSE BEGIN DECLARE @GAO int --This gets the largest GDB_ARCHIVE_OID from the larger --feature class and adds one to it Select @GAO = max(mp.GDB_ARCHIVE_OID) + 1 From CollectorSpatialData.dbo.MONITORINGPOINT mp INSERT INTO CollectorSpatialData.dbo.MONITORINGPOINT ( MonitoringPointID, StateID, Point, StateObjectID, Route, X_Coord, Y_Coord, Notes, RouteOrder, Spring, Fall, DateAdded, DateModified, GDB_ARCHIVE_OID, FocalRefID, long, lat, StatePoint, GlobalID, Shape, GDB_GEOMATTR_DATA, GDB_FROM_DATE, GDB_TO_DATE, OBJECTID) SELECT MonitoringPointID = i.MonitoringPointID, StateID = i.StateID, Point = i.Point, StateObjectID = i.StateObjectID, Route = i.Route, X_coord = i.X_Coord, Y_coord = i.Y_Coord, Notes = i.Notes, RouteOrder = i.RouteOrder, Spring = i.Spring, Fall = i.Fall, DateAdded = i.DateAdded, DateModified = i.DateModified, GDB_ARCHIVE_OID = @GAO, FocalRefID = i.FocalRefID, long = i.long, lat = i.lat, StatePoint = i.StatePoint, GlobalID = i.GlobalID, Shape = i.Shape, GDB_GEOMATTR_DATA = i.GDB_GEOMATTR_DATA, GDB_FROM_DATE = i.GDB_FROM_DATE, GDB_TO_DATE = i.GDB_TO_DATE, OBJECTID = i.ObjectID FROM inserted i END‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ This logic doesn't seem to work because it grabs the smaller feature classes' GAO instead of the larger one. It also does not add 1 to it. This is the error I get where the index it's talking about refers to the primary key GAO in the larger feature class (CollectorSpatialData.dbo.MonitoringPoint). The next GAO in the larger feature class should be 3506. Is what I'm trying to do impossible?

MKF62 · Answer

I have determined this is impossible and may actually be a terrible idea because you're messing with the archive table in an inorganic process. There is too much possibility for error and corruption in the archive.