Выражение вычисляемого столбца

У вас не может быть вычисляемого столбца на основе данных за пределами текущей обновляемой строки. Лучшее, что вы можете сделать, чтобы сделать это автоматическим, — это создать афтер-триггер, который запрашивает всю таблицу, чтобы найти следующее значение для кода продукта. Но для того, чтобы это работало, вам придется использовать эксклюзивную блокировку таблицы, которая полностью уничтожит параллелизм, так что это не очень хорошая идея.

Я также не рекомендую использовать представление, потому что ему придется вычислять ProductCode каждый раз, когда вы читаете таблицу. Это также было бы огромным убийцей производительности. Если вы не сохраните значение в базе данных, чтобы его больше никогда не трогали, ваши коды продуктов могут быть подвержены ложным изменениям (например, в случае удаления ошибочно введенного и никогда не используемого продукта).

Вот что я рекомендую вместо этого. Создайте новую таблицу:

dbo.SellerProductCode

SellerID  LastProductCode
--------  ---------------
  1        3
  2        1

Эта таблица надежно записывает последний использованный код продукта для каждого продавца. На INSERT в вашу таблицу Product триггер обновит LastProductCode в этой таблице соответствующим образом для всех затронутых SellerID, а затем обновит все вновь вставленные строки в таблице Product соответствующими значениями. Это может выглядеть примерно так, как показано ниже.

Посмотрите, как этот триггер работает в скрипте Sql

CREATE TRIGGER TR_Product_I ON dbo.Product FOR INSERT
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @LastProductCode TABLE (
   SellerID int NOT NULL PRIMARY KEY CLUSTERED,
   LastProductCode int NOT NULL
);

WITH ItemCounts AS (
   SELECT
      I.SellerID,
      ItemCount = Count(*)
   FROM
      Inserted I
   GROUP BY
      I.SellerID
)
MERGE dbo.SellerProductCode C
USING ItemCounts I
   ON C.SellerID = I.SellerID
WHEN NOT MATCHED BY TARGET THEN
   INSERT (SellerID, LastProductCode)
   VALUES (I.SellerID, I.ItemCount)
WHEN MATCHED THEN
   UPDATE SET C.LastProductCode = C.LastProductCode + I.ItemCount
OUTPUT
   Inserted.SellerID,
   Inserted.LastProductCode
INTO @LastProductCode;

WITH P AS (
   SELECT
      NewProductCode =
         L.LastProductCode + 1
         - Row_Number() OVER (PARTITION BY I.SellerID ORDER BY P.ProductID DESC),
      P.*
   FROM
      Inserted I
      INNER JOIN dbo.Product P
            ON I.ProductID = P.ProductID
      INNER JOIN @LastProductCode L
         ON P.SellerID = L.SellerID
)
UPDATE P
SET P.ProductCode = Right('00000' + Convert(varchar(6), P.NewProductCode), 6);

Обратите внимание, что этот триггер работает, даже если вставлено несколько строк. Также нет необходимости предварительно загружать таблицу SellerProductCode — новые продавцы будут добавлены автоматически. Это будет обрабатывать параллелизм с несколькими проблемами. Если возникают проблемы параллелизма, можно добавить правильные подсказки блокировки без вредного эффекта, поскольку таблица останется очень маленькой и можно будет использовать ROWLOCK (за исключением INSERT, для которого потребуется блокировка диапазона).

Пожалуйста, см. Sql Fiddle для рабочего, протестированного кода, демонстрирующего технику. Теперь у вас есть настоящие коды продуктов, которые не нужно менять и которые будут надежными.

Обычно я бы рекомендовал использовать представление для такого типа вычислений. Представление можно даже проиндексировать, если производительность выбора является наиболее важным фактором (я вижу, вы используете persisted).

У вас не может быть подзапроса в вычисляемом столбце, что по сути означает, что вы можете получить доступ только к данным в текущей строке. Единственным способом получить это количество было бы использование определяемой пользователем функции в вычисляемом столбце или запускает обновление невычисляемого столбца.

Представление может выглядеть следующим образом:

create view ProductCodes as
select p.ProductId, p.SellerId,
    (
            select right('000000' + cast(count(*) as varchar(6)), 6)
            from Product
            where SellerID = p.SellerID
                and ProductID <= p.ProductID
    ) as ProductCode
from Product p

Одно большое предостережение к вашей схеме нумерации продуктов и недостаток как для представления, так и для опций UDF заключается в том, что мы полагаемся на количество строк с более низким ProductId. Это означает, что если Продукт будет вставлен в середину последовательности, он фактически изменит ProductCodes существующих Продуктов с более высоким ProductId. В этот момент вы должны либо:

• Гарантировать последовательность ProductId (сама по себе идентификация этого не делает)
• Полагаться на другой столбец с гарантированной последовательностью (все еще сомнительно, но, может быть, CreateDate?)
• Используйте триггер, чтобы получить количество при вставке, которое затем никогда не изменяется.