1

There is a Enterprise geodatabase (MS SQL Server, ArcGIS), it has about 100 feature classes. It is necessary to automate the process of populating a unique number to each object in the database. numbers should not be repeated in all database. I created a SQL trigger that successfully filled the number. But base settings are without flag "move edits to base", and the trigger does not work, because it goes through the delta table. When the flag is true triggers work well. But I shouldn't use this flag. Any ideas how to do this without triggers?

CREATE TRIGGER DBO.UNIQ_id
    ON DBO.LAND_POL
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @MAXCOUNTLAST AS INT
    DECLARE @MAXCOUNTNEW AS INT
    SELECT @MAXCOUNTLAST=MAX(IDC_MAX.IDC) FROM DBO.IDC_MAX
    SELECT ONJECTID, IDC, ROW_NUMBER() OVER (ODER BY OBJECTID) AS aID INTO #T1 FROM INSERTED WHERE (IDC IS NULL);
    UPDATE #T1 SET aID = aID+@MAXCOUNTLAST
    UPDATE DBO.LAND_POL SET IDC=#T1.aID FROM DBO.LAND_POL A INNER JION #T1 ON A.OBJECTID=#T1.OBJECTID
    SELECT @MAXCOUNTNEW=MAX(LAND.POL.IDC) FROM DBO.LAND_POL
    UPDATE DBO.IDC_MAX SET IDC = @MAXCOUNTNEW
END
  • You appear to have two problems here. One is to assign IDs to all existing features, and the second is to append IDs to new features as they are added. Since a trigger a the most appropriate tool to do the latter, I suggest you focus this question on the exact code you used, with details on where you applied the trigger. – Vince Jul 1 at 13:08
  • I 've already solved the first problem whith temp table, that contains last ID. the trigger code, I will write in a few minutes – Lora Jul 1 at 13:15
  • And how about the second question?) – Lora Jul 1 at 15:50
  • You should probably review how Esri implements the "i-tables" to allocate objectid values. What you have here is far too expensive to execute with a bulk-insert. – Vince Jul 1 at 16:18
  • @Vince , we will not have a bulk-insert. – Lora Jul 1 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.