SDE自动ID插入多个记录 [英] SDE Auto ID on Insert multiple records

问题描述

所以这里是我的情况。我有数据位于SQL Server 2008 R2版本的ESRI SDE数据库，我想自动填充一个唯一的ID。我发现一个脚本在单个记录插入（它也适用于多个记录），它将拉从表中的最大ID，并为插入添加一个数字。

So here is my situation. I have data located in a SQL Server 2008 R2 versioned ESRI SDE database which I want to have auto-populate a unique ID for. I have found a script that works well on a single record insert (it works also for multiple records) which will pull the max ID from the tables and add one number to it for the insert.

我遇到的问题是由于ESRI用于插入记录的方式。当您在地图软件（ArcMap）中创建数据时，每次点击保存时，它都会作为表的插入。因此，如果您在一个记录后点击保存，并且最大ID为27，则新记录/数据的ID将为28.再次使用此情况，但是这次您在点击保存之前创建了两个功能，这将两个记录插入表中同时每个记录的ID分别为28，而不是28和29.

The problem I have run into is due to the way ESRI works for inserting records. When you create data in your mapping software (ArcMap) every time you hit save it acts as an insert on the table. So if you were to hit save after one record and the max ID is 27 the ID for the new record/data would be 28. Take this case again but this time you created two features before hitting save, this inserts both records into the table at the same time and each record gets an ID of 28 instead of 28 and 29 respectively.

所有这一切，我已经粘贴了我下面使用的代码对原始数据集和从它的版本化创建的增量表，但我需要帮助找出是如何使用来自当前数据集的最大ID填充插入的每个记录的下一个记录ID，而不重复它在多个记录插。此代码专注于宗地边界的宗地ID，在许多情况下，我可能必须拆分宗地，这将导致需要唯一ID的两个功能。欢迎任何和所有建议。

All this being said, I have pasted the code I am using below which works on both the original dataset and the delta table created from it being versioned, but what I need help figuring out is how to use the max ID from the current dataset to populate the next record ID for each record inserted without duplicating it on a multiple record insert. This code is focused on a parcel id for a parcel boundary which in many cases I may have to split a parcel which would result in two features needing a unique ID. Any and all advice is welcomed and appreciated

CREATE TRIGGER [dbo].[PARCEL_AUTO_ID]
    ON [dbo].[a405]
    FOR INSERT, UPDATE
AS
BEGIN
    DECLARE @Max_Value int
    SET @Max_Value = (SELECT MAX(PARCEL_ID) FROM [dbo].[TOWN_PARCELS])+1

    UPDATE [dbo].[a405]
    set PARCEL_ID = @Max_Value
        WHERE PARCEL_ID IS NULL

    UPDATE [dbo].[TOWN_BUILDING_FOOTPRINTS]
    set PARCEL_ID = @Max_Value
        WHERE PARCEL_ID IS NULL
END

推荐答案

我想出来了。它应该是这样的。

I figured it out finally. It should look like this.

CREATE TRIGGER [dbo].[PARCEL_AUTO_ID]
    ON [dbo].[a405]
    FOR INSERT, UPDATE
AS
BEGIN
    DECLARE @Max_Value int
    SET @Max_Value = (SELECT MAX(PARCEL_ID) FROM [dbo].[a405])+1

    UPDATE [dbo].[a405]
    set PARCEL_ID = @Max_Value
        WHERE PARCEL_ID IS NULL

    UPDATE [dbo].[TOWN_BUILDING_FOOTPRINTS]
    set PARCEL_ID = @Max_Value
        WHERE PARCEL_ID IS NULL
END

这篇关于SDE自动ID插入多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！