Вместо запуска в SQL Server теряет SCOPE_IDENTITY?

Ответ 1

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

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

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

Ответ 2

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

Ответ 3

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

Но есть обходное решение для принудительного присвоения @@identity значения scope_identity().

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

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

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

Теперь, как окончательно сделать ваш триггер безопасным для @@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.

Update:

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

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

Ответ 4

Как и комментарий 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

Ответ 5

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

В процедуре:

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).

Ответ 6

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

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

SELECT * FROM deleted UNION ALL
SELECT * FROM inserted;

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

SELECT IDENT_CURRENT(‘tablename’) AS <IdentityColumnname>