Вместо триггера в SQL Server теряет SCOPE_IDENTITY?

Используйте @@identity вместо scope_identity().

В то время как scope_identity() возвращает последний созданный идентификатор в текущей области, @@identity возвращает последний созданный идентификатор в текущем сеансе.

Обычно рекомендуется использовать функцию scope_identity() вместо поля @@identity, поскольку обычно вы не хотите, чтобы триггеры мешали идентификатору, но в этом случае вы это делаете.

Поскольку вы используете SQL 2008, я настоятельно рекомендую использовать предложение OUTPUT вместо одной из пользовательских функций идентификации. SCOPE_IDENTITY в настоящее время имеет некоторые проблемы с параллельными запросами, которые заставляют меня полностью отказаться от него. @@Identity нет, но он все же не такой явный и гибкий, как OUTPUT. Плюс OUTPUT обрабатывает многострочные вставки. Взгляните на статью BOL, в которой есть несколько замечательных примеров.

У меня были серьезные сомнения по поводу использования @@identity, потому что это может вернуть неверный ответ.

Но есть обходной путь, чтобы заставить @@identity иметь значение scope_identity().

Просто для полноты сначала я перечислю пару других обходных путей для этой проблемы, которые я видел в Интернете:

1. Заставьте триггер возвращать набор строк. Затем в оболочке SP, выполняющей вставку, выполните INSERT Table1 EXEC sp_ExecuteSQL ... еще одну таблицу. Тогда будет работать scope_identity(). Это грязно, потому что требует динамического SQL, что является проблемой. Кроме того, имейте в виду, что динамический SQL выполняется с разрешениями пользователя, вызывающего SP, а не с разрешениями владельца SP. Если первоначальный клиент мог вставлять в таблицу, он все еще должен иметь это разрешение, просто знайте, что вы можете столкнуться с проблемами, если откажете в разрешении на вставку непосредственно в таблицу.

2. Если есть другой ключ-кандидат, получите идентификатор вставленных строк, используя эти ключи. Например, если имя имеет уникальный индекс, вы можете вставить, а затем выбрать идентификатор (максимум для нескольких строк) из таблицы, которую вы только что вставили, используя имя. Хотя это может привести к проблемам параллелизма, если другой сеанс удалит только что вставленную строку, это не хуже, чем в исходной ситуации, если кто-то удалит вашу строку до того, как приложение сможет ее использовать.

Теперь, вот как окончательно сделать ваш триггер безопасным, чтобы @@Identity возвращал правильное значение, даже если ваш SP или другой триггер выполняет вставку в таблицу, содержащую идентификационные данные, после основной вставки.

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

CREATE TRIGGER TR_MyTable_I ON MyTable INSTEAD OF INSERT
AS
SET NOCOUNT ON

DECLARE @MyTableID int
INSERT MyTable (Name, SystemUser)
SELECT I.Name, System_User
FROM Inserted

SET @MyTableID = Scope_Identity()

INSERT AuditTable (SystemUser, Notes)
SELECT SystemUser, 'Added Name ' + I.Name
FROM Inserted

-- The following statement MUST be last in this trigger. It resets @@Identity
-- to be the same as the earlier Scope_Identity() value.
SELECT MyTableID INTO #Trash FROM MyTable WHERE MyTableID = @MyTableID

Обычно дополнительная вставка в таблицу аудита нарушает все, потому что, поскольку в ней есть столбец идентификаторов, @@Identity вернет это значение, а не значение из вставки в MyTable. Однако окончательный выбор создает новое значение @@Identity, которое является правильным, на основе Scope_Identity(), которую мы сохранили ранее. Это также защищает его от любого возможного дополнительного триггера AFTER в таблице MyTable.

Обновление:

Я только что заметил, что триггер INSTEAD OF здесь не нужен. Это делает все, что вы искали:

CREATE TRIGGER dbo.TR_Payments_Insert ON dbo.Payment FOR INSERT
AS 
SET NOCOUNT ON;
IF EXISTS (
   SELECT *
   FROM
      Inserted I
      INNER JOIN dbo.Payment P ON I.CustomerID = P.CustomerID
   WHERE
      I.DateFrom < P.DateTo
      AND P.DateFrom < I.DateTo
) ROLLBACK TRAN;

Это, конечно, позволяет продолжать работу scope_identity(). Единственным недостатком является то, что вставка с откатом в таблицу идентификаторов использует используемые значения идентификатора (значение идентификатора по-прежнему увеличивается на количество строк в попытке вставки).

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

Основная проблема: Trigger и Entity framework работают в разной области. Проблема в том, что если вы сгенерируете новое значение PK в триггере, это будет другая область действия. Таким образом, эта команда возвращает нулевые строки, и EF выдает исключение.

Решение состоит в том, чтобы добавить следующий оператор SELECT в конец триггера:

SELECT * FROM deleted UNION ALL
SELECT * FROM inserted;

вместо * вы можете указать все имена столбцов, включая

SELECT IDENT_CURRENT(‘tablename’) AS <IdentityColumnname>

Как прокомментировал araqnid, триггер, похоже, откатывает транзакцию при выполнении условия. Вы можете сделать это проще с помощью триггера AFTER INSTERT:

CREATE TRIGGER [dbo].[TR_Payments_Insert]
   ON  [dbo].[Payment]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    IF <Condition>
    BEGIN
        ROLLBACK TRANSACTION
    END
END

Затем вы можете снова использовать SCOPE_IDENTITY(), потому что INSERT больше не выполняется в триггере.

Само условие, по-видимому, пропускает две одинаковые строки, если они находятся в одной и той же вставке. С помощью триггера AFTER INSERT вы можете переписать условие следующим образом:

IF EXISTS(
    SELECT *
    FROM dbo.Payment a
    LEFT JOIN dbo.Payment b
        ON a.Id <> b.Id
        AND a.CustomerId = b.CustomerId
        AND (a.DateFrom BETWEEN b.DateFrom AND b.DateTo
        OR a.DateTo BETWEEN b.DateFrom AND b.DateTo)
    WHERE b.Id is NOT NULL)

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

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

create procedure dbo.InsertPayment
    @DateFrom datetime, @DateTo datetime, @CustomerId int, @AdminId int
as
BEGIN TRANSACTION

IF NOT EXISTS (
    SELECT *
    FROM dbo.Payment
    WHERE CustomerId = @CustomerId
    AND (@DateFrom BETWEEN DateFrom AND DateTo
    OR @DateTo BETWEEN DateFrom AND DateTo))
    BEGIN

    INSERT into dbo.Payment 
    (DateFrom, DateTo, CustomerId, AdminId)
    VALUES (@DateFrom, @DateTo, @CustomerId, @AdminId)

    END
COMMIT TRANSACTION

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

В процедуре:

CREATE table #temp ( id int )

... insert statement ...

select id from #temp
-- (you can add sorting and top 1 selection for extra safety)

drop table #temp

Вместо триггера:

-- this check covers you for any inserts that don't want an identity value returned (and therefore don't provide a temp table)
IF OBJECT_ID('tempdb..#temp') is not null
begin
    insert into #temp(id)
    values
    (SCOPE_IDENTITY())
end

Вы, вероятно, захотите назвать его как-то иначе, чем #temp, из соображений безопасности (что-то достаточно длинное и случайное, чтобы никто другой не использовал его: #temp1234235234563785635).