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

У меня есть таблица, где я создалINSTEAD OF триггер для обеспечения соблюдения некоторых бизнес-правил.

Проблема в том, что когда я вставляю данные в эту таблицу,SCOPE_IDENTITY() возвращаетNULL значение, а не фактическая вставленная личность.

Вставить + код области
INSERT INTO [dbo].[Payment]([DateFrom], [DateTo], [CustomerId], [AdminId])
VALUES ('2009-01-20', '2009-01-31', 6, 1)

SELECT SCOPE_IDENTITY()
Спусковой крючок:
CREATE TRIGGER [dbo].[TR_Payments_Insert]
   ON  [dbo].[Payment]
   INSTEAD OF INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF NOT EXISTS(SELECT 1 FROM dbo.Payment p
              INNER JOIN Inserted i ON p.CustomerId = i.CustomerId
              WHERE (i.DateFrom >= p.DateFrom AND i.DateFrom <= p.DateTo) OR (i.DateTo >= p.DateFrom AND i.DateTo <= p.DateTo)
              ) AND NOT EXISTS (SELECT 1 FROM Inserted p
              INNER JOIN Inserted i ON p.CustomerId = i.CustomerId
              WHERE  (i.DateFrom <> p.DateFrom AND i.DateTo <> p.DateTo) AND 
              ((i.DateFrom >= p.DateFrom AND i.DateFrom <= p.DateTo) OR (i.DateTo >= p.DateFrom AND i.DateTo <= p.DateTo))
              )

    BEGIN
        INSERT INTO dbo.Payment (DateFrom, DateTo, CustomerId, AdminId)
        SELECT DateFrom, DateTo, CustomerId, AdminId
        FROM Inserted
    END
    ELSE
    BEGIN
            ROLLBACK TRANSACTION
    END


END

Код работал до создания этого триггера. Я использую LINQ to SQL в C #. Я не вижу способа изменитьSCOPE_IDENTITY в@@IDENTITY, Как мне сделать эту работу?

 Robert Harvey♦26 мая 2009 г., 00:30
Я так понимаю, эта таблица работала до того, как вы вставили оператор INSTEAD OF? Вы проверили свое поле первичного ключа, чтобы убедиться, что оно имеет спецификацию Identity?
 araqnid26 мая 2009 г., 01:04
Почему бы не использовать триггер BEFORE INSERT, который выдает ошибку, если правила не выполняются, а не INSTEAD OF?
 kastermester26 мая 2009 г., 01:42
@araqnid - потому что, насколько я мог видеть, SQL Server не имеет такой вещи - хотя я должен признать, что я не пробовал напрямую, но исходя из этого из поиска в Google, может быть, конечно, я обнаружил недействительные или устаревшие ресурсы - есть ли на самом деле такая вещь?
 kastermester26 мая 2009 г., 00:35
Да и да, это действительно так (вы можете увидеть код сейчас - он вставляет строку и получает автоматическую идентификацию при вставке).
 Robert Harvey♦26 мая 2009 г., 00:30
Можем ли мы увидеть SQL?

Ответы на вопрос(6)

потому что это может вернуть неправильный ответ.

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

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

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

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

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

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

 Peter Radocchia07 мар. 2010 г., 04:31
это очень креативно
 ErikE09 мар. 2010 г., 02:05
Спасибо, парни! Я понял это несколько лет назад после мучительных проблем в проекте доступа к данным (ADP), который использует @@ Identity вместо Scope_Identity. Я отчаянно хотел использовать триггер INSTEAD OF UPDATE, чтобы связать формы с представлением и сделать его обновляемым. Я наконец получил это работает! (Для полноты обратите внимание, что для этого требуется WITH VIEW_METADATA, чтобы Access не запрашивал базовые таблицы самостоятельно.)
 ErikE09 мар. 2010 г., 02:28
Вполне возможно, что производительность может быть улучшена, если снова не ударить исходную таблицу в конце, создать #Trash, затем включить identity_insert и выполнить вставку с @MyTableID.
 kastermester07 мар. 2010 г., 15:10
Это действительно блестяще - хотя мне больше не нужно решение для этого - для других и, возможно, для меня самого в будущем, это просто потрясающе :)
Решение Вопроса

@@identity вместоscope_identity().

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

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

 Guffa06 мар. 2010 г., 20:21
@Emtucifor: В большинстве случаев вам нужно то, что возвращает scope_identity (), но не в этом случае. На этот раз это правильный выбор @@. Причина того, что оба существуют, состоит в том, что иногда вы действительно хотите необычный результат.
 kastermester26 мая 2009 г., 00:36
После того, как я добавил еще немного контента к своему сообщению, вы можете видеть, что я использую LINQ to SQL в своем приложении .Net, поэтому у меня действительно нет большого выбора, насколько я могу видеть, за исключением, возможно, использования sproc для вставки данные.
 ErikE07 мар. 2010 г., 01:32
Я понимаю, что вы не можете предполагать, что при вставке в таблицу с триггером INSTEAD OF строки вообще будут даже добавлены в таблицу. Они могут быть помещены в другую таблицу, или во многие другие таблицы, или вообще никуда не вставлены. Однако вызывающий скрипт не знает этого и не должен знать об этом. Смысл триггера INSTEAD OF состоит в том, чтобы сделать грязные кишки базовой операции с данными прозрачными для клиента, который выполняет вставку. На мой взгляд, должен быть какой-то способ явно установить scope_identity в триггере INSTEAD OF.
 ErikE06 мар. 2010 г., 20:01
У меня есть серьезные сомнения относительно использования @@ identity, но я думаю, что в этом узком случае это нормально, поскольку другие обходные пути не намного лучше, и, по крайней мере, вы знаете, что это даст правильное значение, если триггер не вставляется в другой стол тем временем.
 ErikE07 мар. 2010 г., 00:47
Хаффа, ты хочешь только @@ identity, потому что scope_identity () не выполняет функцию, которую мы все ожидаем от нее: ты вставляешь в таблицу, в которой есть столбец идентификаторов, а затем хочешь вернуть значение этого столбца идентификаторов. Предполагается, что Scope_Identity () защищает вас от необходимости искать, а затем тщательно проверять любые триггеры в таблице, чтобы убедиться, что они не вставляются в другую таблицу со столбцом идентификаторов. Но неограниченная рекомендация, которую вы дали использовать @@ identity, нарушит момент, когда на столе будет еще один триггер после вставки или вставка вместо триггера во вторую таблицу.

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

В процедуре:

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

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

 Nathan20 дек. 2012 г., 17:50
Кроме того, предложение OUTPUT всегда возвращает 0 для столбцов идентификаторов, если в таблице существует триггер INSTEAD OF INSERT.
 Parth Shah31 июл. 2014 г., 11:02
Для тех, кто никогда не слышал о предложении OUTPUT и не использовал его, есть ветка, объясняющая, как его использовать:stackoverflow.com/questions/10999396/...
 ErikE07 мар. 2010 г., 01:35
Дело в параллельных запросах хорошо. Но @@ identity отлично справляется с многострочными вставками:INSERT TheTable ... | SELECT @LastID = Scope_Identity(), @Rows = @@RowCount | SELECT FROM TheTable WHERE ID BETWEEN @LastID - @Rows + 1 AND @LastID
 Aaron Alton27 мая 2009 г., 04:07
Извините, я должен был опубликовать ссылку для подключения:connect.microsoft.com/SQLServer/feedback/...
 HLGEM26 мая 2009 г., 19:14
какие проблемы с параллельными запросами? они находятся в другой области, поэтому я не вижу, где они могут быть проблемой. у symb_current есть проблемы с параллельными вставками, а у @@ identity есть проблемы со вставками из триггеров, именно поэтому scope_identity был предпочтительным методом до изобретения вывода. Однако совет использовать Output очень хорош.

триггер, похоже, выполняет откат транзакции при выполнении условия. Вы можете сделать это проще с помощью триггера 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
 kastermester26 мая 2009 г., 12:11
Спасибо за примеры и советы, я ознакомился с ними и, вероятно, воспользуюсь некоторыми вашими идеями, однако не все из них применимы в этом случае. Кроме того, да, я не проверяю столбец Id, я позволяю БД обрабатывать это для меня (вместе с парой других ограничений), однако я, вероятно, перенесу свой триггер на после вставки - мне совершенно не удалось увидеть логическое решение, когда я создал мой триггер :)
 Andomar09 мар. 2010 г., 12:46
@Emtucifor: Ссылка, которую вы даете, предполагает EndA> StartA, на которую я бы не рассчитывал, если бы она не обеспечивалась проверочными ограничениями. Триггер, который применяет правила целостности, должен быть заменен проверочным ограничением (необязательно с UDF.) Триггеры являются злыми и должны быть устранены.
 ErikE09 мар. 2010 г., 02:26
Кроме того, можем ли мы получить некоторые ссылки на то, как хранимые процедуры являются и SIMPLER и FASTER, чем триггеры? Триггер часто проще, потому что он может абсолютно обеспечить соблюдение правил целостности данных, но наличие процедуры не гарантирует, что она всегда будет использоваться (не дайте мне сказать, что приложение использует только SP, когда-нибудь кто-нибудь вставит несколько строк где-то через задний конец). И я скептически отношусь к тому, что быстрее. Вы можете доказать это?
 ErikE10 мар. 2010 г., 21:55
@ Andomar: это неправильно. Нет предположения, что EndA> StartA. Посмотри пожалуйстаsilentmatt.com/intersection.html для помощи визуализации, как это работает для ВСЕХ диапазонов. Во-вторых, не могли бы вы предоставить источник или какую-нибудь научную работу о том, как триггеры всегда являются злом и всегда должны быть устранены? Я согласен с тем, что они часто чрезмерно используются, неправильно применяются или плохо написаны, и если проверка или ограничение внешнего ключа могут сделать работу, они неуместны. Тем не менее, триггеры более надежны, чем SP. Очень часто люди говорят, что «приложение использует только SP», но однажды кто-то вставляет данные напрямую.
 ErikE09 мар. 2010 г., 02:24
@ Andomar, использование BETWEEN означает, что сервер должен проверить четыре условия. Вы можете обойтись вдвое меньше. Видетьstackoverflow.com/questions/325933/... Больше подробностей.

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

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

SELECT * FROM deleted UNION ALL
SELECT * FROM inserted;

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

SELECT IDENT_CURRENT(‘tablename’) AS <IdentityColumnname>

Ваш ответ на вопрос