Database Triggers: GDB_ARCHIVE_OIDS out of sync with regular OIDs
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
ENDThis 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?
