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

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

Как и прокомментировал 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)

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

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

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

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

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

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

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

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

Вот как окончательно сделать ваш триггер безопасным для @@ 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 () продолжать работать. Единственный недостаток заключается в том, что при откате вставки в таблицу идентификаторов используются используемые значения идентификаторов (значение идентификатора по-прежнему увеличивается на количество строк при попытке вставки).

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