Как я могу автоматически увеличивать столбец без использования IDENTITY?

Я создаю таблицу с двумя столбцами, которые я хочу автоматически увеличивать. Один столбец является первичным ключом, поэтому я использую ключевое слово IDENTITY. Другой столбец будет использоваться для отслеживания пользовательского "порядка сортировки" элементов в таблице. Каждый раз, когда пользователь перемещает элемент, его "порядок сортировки" меняет значения с помощью значения другого элемента. Однако, когда элемент вставлен в таблицу, вставленный элемент всегда должен автоматически присваивать значение порядка сортировки выше любого другого значения в таблице. Здесь упрощенная версия создания таблицы script:

CREATE TABLE [AnswerRow] (
    [AnswerRowId] [int] IDENTITY(1,1) NOT NULL,
    [SortOrder] [int] NOT NULL,
    [IsDeleted] [bit] NOT NULL CONSTRAINT [DF_AnswerRow_IsDeleted] DEFAULT 0,
    CONSTRAINT [PK_AnswerRow] PRIMARY KEY CLUSTERED ([AnswerRowId] asc)
)

Какой лучший способ сделать столбец SortOrder автоматическим приращением таким же образом, как столбец AnswerRowId (но все же сможет изменить значения порядка сортировки после этого)?

Ответ 1

Я не уверен, что это то, что имел в виду @Stephen Wrighton, но я думаю, что у вас может быть триггер insert использовать значение IDENTITY, генерируемое для AnswerRowId:

CREATE TRIGGER [dbo].[AnswerRowInsertTrigger]
   ON  [dbo].[AnswerRow]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE a SET a.SortOrder = a.AnswerRowId
    FROM AnswerRow a JOIN inserted i ON a.AnswerRowId = i.AnswerRowId

END

Ответ 2

Два способа сразу приходят в голову, первый - это триггер, что не то, что я сделал бы лично.

Во-вторых, было бы, чтобы мой sql сделал что-то в этом направлении:

INSERT INTO AnswerRow(SortOrder, IsDeleted, Answer)
SELECT MAX(SortOrder) + 1, 0, 'My New Answer'
FROM AnswerRow

Ответ 3

Для того, что стоит, я нашел гораздо легче использовать столбец с плавающей запятой для позиции сортировки, чем использовать целочисленное значение. Для новых записей вам все равно нужен триггер, который устанавливает значение, равное наибольшему существующему значению плюс некоторая константа (скажем, 100). Однако, чтобы обновить позицию сортировки элемента, вы можете просто найти позицию сортировки записи до и после записи, которую вы перемещаете. Выровняйте эти два значения, и у вас есть новая позиция сортировки.

Ответ 4

DECLARE @NextItem int

SET @NextItem =    (SELECT 
       COUNT(*) + 1 AS NewSortOrder
    FROM 
       MyTable 
    WHERE 
       [email protected] 
    ORDER BY 
       SortOrder ASC)


   --now you can use @NextItem
   INSERT INTO AnswerRow(SortOrder, IsDeleted) VALUES(@NextItem, 0)

Или вы можете просто использовать текущий порядок:

 SELECT TOP 1 (ISNULL(SortOrder, 0) + 1) as NewSortOrder FROM AnswerRow WHERE [email protected] ORDER BY SortOrder DESC

Возможно, вам захочется проверить, возвращает ли это NULL в случае отсутствия записей, проверьте IsNULL в BOL.

В любом случае, вы хотите получить текущий номер, добавить его к нему, а затем использовать это значение для своей вставки.