而不是SQL Server中的触发器丢失SCOPE_IDENTITY？

I have a table where I created an INSTEAD OF trigger to enforce some business rules.

我有一个表，我创建了一个INSTEAD OF触发器来强制执行一些业务规则。

The issue is that when I insert data into this table, SCOPE_IDENTITY() returns a NULL value, rather than the actual inserted identity.

问题是当我将数据插入此表时，SCOPE_IDENTITY（）返回NULL值，而不是实际插入的标识。

Insert + Scope code

INSERT INTO [dbo].[Payment]([DateFrom], [DateTo], [CustomerId], [AdminId])
VALUES ('2009-01-20', '2009-01-31', 6, 1)

SELECT SCOPE_IDENTITY()

Trigger:

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

The code worked before the creation of this trigger. I am using LINQ to SQL in C#. I don't see a way of changing SCOPE_IDENTITY to @@IDENTITY. How do I make this work?

代码在创建此触发器之前有效。我在C＃中使用LINQ to SQL。我没有看到将SCOPE_IDENTITY更改为@@ IDENTITY的方法。我该如何工作？

6 个解决方案

#1

Use @@identity instead of scope_identity().

使用@@ identity而不是scope_identity（）。

While scope_identity() returns the last created id in the current scope, @@identity returns the last created id in the current session.

当scope_identity（）返回当前作用域中最后创建的id时，@@ identity将返回当前会话中最后创建的id。

The scope_identity() function is normally recommended over the @@identity field, as you usually don't want triggers to interfer with the id, but in this case you do.

通常建议在@@ identity字段上使用scope_identity（）函数，因为您通常不希望触发器干扰id，但在这种情况下，您可以这样做。

#2

Since you're on SQL 2008, I would highly recommend using the OUTPUT clause instead of one of the custom identity functions. SCOPE_IDENTITY currently has some issues with parallel queries that cause me to recommend against it entirely. @@Identity does not, but it's still not as explicit, and as flexible, as OUTPUT. Plus OUTPUT handles multi-row inserts. Have a look at the BOL article which has some great examples.

由于您使用的是SQL 2008，我强烈建议您使用OUTPUT子句而不是其中一个自定义标识函数。 SCOPE_IDENTITY目前在并行查询方面存在一些问题，导致我建议完全反对它。 @@ Identity没有，但它仍然不像OUTPUT那样明确，灵活。 Plus OUTPUT可处理多行插入。看一下有一些很好例子的BOL文章。

#3

I was having serious reservations about using @@identity, because it can return the wrong answer.

我对使用@@ identity有严重保留意见，因为它可能会返回错误的答案。

But there is a workaround to force @@identity to have the scope_identity() value.

但是有一种解决方法可以强制@@ identity拥有scope_identity（）值。

Just for completeness, first I'll list a couple of other workarounds for this problem I've seen on the web:

为了完整起见，我首先列出了我在网上看到的这个问题的其他几个解决方法：

Make the trigger return a rowset. Then, in a wrapper SP that performs the insert, do INSERT Table1 EXEC sp_ExecuteSQL ... to yet another table. Then scope_identity() will work. This is messy because it requires dynamic SQL which is a pain. Also, be aware that dynamic SQL runs under the permissions of the user calling the SP rather than the permissions of the owner of the SP. If the original client could insert to the table, he should still have that permission, just know that you could run into problems if you deny permission to insert directly to the table.

使触发器返回行集。然后，在执行插入的包装器SP中，执行INSERT Table1 EXEC sp_ExecuteSQL ...到另一个表。然后scope_identity（）将起作用。这很麻烦，因为它需要动态SQL，这很痛苦。此外，请注意，动态SQL在调用SP的用户的权限下运行，而不是在SP的所有者的权限下运行。如果原始客户端可以插入到表中，他仍然应该具有该权限，只要知道如果拒绝直接插入表的权限，则可能会遇到问题。
If there is another candidate key, get the identity of the inserted row(s) using those keys. For example, if Name has a unique index on it, then you can insert, then select the (max for multiple rows) ID from the table you just inserted to using Name. While this may have concurrency problems if another session deletes the row you just inserted, it's no worse than in the original situation if someone deleted your row before the application could use it.

如果存在另一个候选键，则使用这些键获取插入行的标识。例如，如果Name上有唯一索引，则可以插入，然后使用Name从刚刚插入的表中选择（多行多行）ID。虽然如果另一个会话删除您刚刚插入的行，这可能会出现并发问题，但如果有人在应用程序使用它之前删除了您的行，则不会比原始情况更糟糕。

Now, here's how to definitively make your trigger safe for @@Identity to return the correct value, even if your SP or another trigger inserts to an identity-bearing table after the main insert.

现在，即使您的SP或其他触发器在主插入后插入到标识承载表中，也可以使用@@ Identity确定触发器的安全性，以便返回正确的值。

Also, please put comments in your code about what you are doing and why so that future visitors to the trigger don't break things or waste time trying to figure it out.

此外，请在您的代码中添加关于您正在做什么的评论以及为什么触发器的未来访问者不会破坏事物或浪费时间来解决问题。

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

Normally, the extra insert to the audit table would break everything, because since it has an identity column, then @@Identity will return that value instead of the one from the insertion to MyTable. However, the final select creates a new @@Identity value that is the correct one, based on the Scope_Identity() that we saved from earlier. This also proofs it against any possible additional AFTER trigger on the MyTable table.

通常，审计表的额外插入会破坏所有内容，因为它具有标识列，因此@@ Identity将返回该值而不是从插入到MyTable的值。但是，最终选择会根据我们之前保存的Scope_Identity（）创建一个新的@@ Identity值，该值是正确的。这也证明它可以防止MyTable表上任何可能的额外AFTER触发器。

Update:

更新：

I just noticed that an INSTEAD OF trigger isn't necessary here. This does everything you were looking for:

我刚才注意到这里不需要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;

This of course allows scope_identity() to keep working. The only drawback is that a rolled-back insert on an identity table does consume the identity values used (the identity value is still incremented by the number of rows in the insert attempt).

这当然允许scope_identity（）继续工作。唯一的缺点是标识表上的回滚插入确实使用了所使用的标识值（标识值仍然增加了插入尝试中的行数）。

I've been staring at this for a few minutes and don't have absolute certainty right now, but I think this preserves the meaning of an inclusive start time and an exclusive end time. If the end time was inclusive (which would be odd to me) then the comparisons would need to use <= instead of <.

我一直盯着这几分钟并且现在没有绝对的确定性，但我认为这保留了包容性开始时间和独家结束时间的含义。如果结束时间是包容性的（这对我来说很奇怪），则比较需要使用<=而不是<。

#4

Like araqnid commented, the trigger seems to rollback the transaction when a condition is met. You can do that easier with an AFTER INSTERT trigger:

像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

Then you can use SCOPE_IDENTITY() again, because the INSERT is no longer done in the trigger.

然后，您可以再次使用SCOPE_IDENTITY（），因为在触发器中不再执行INSERT。

The condition itself seems to let two identical rows past, if they're in the same insert. With the AFTER INSERT trigger, you can rewrite the condition like:

条件本身似乎让两个相同的行过去，如果它们在同一个插入中。使用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)

And it will catch duplicate rows, because now it can differentiate them based on Id. It also works if you delete a row and replace it with another row in the same statement.

它会捕获重复的行，因为现在它可以根据Id区分它们。如果删除一行并将其替换为同一语句中的另一行，它也可以工作。

Anyway, if you want my advice, move away from triggers altogether. As you can see even for this example they are very complex. Do the insert through a stored procedure. They are simpler and faster than triggers:

无论如何，如果你需要我的建议，完全放弃触发器。正如您所看到的，即使是这个例子，它们也非常复杂。通过存储过程执行插入操作。它们比触发器更简单，更快捷：

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

A little late to the party, but I was looking into this issue myself. A workaround is to create a temp table in the calling procedure where the insert is being performed, insert the scope identity into that temp table from inside the instead of trigger, and then read the identity value out of the temp table once the insertion is complete.

派对有点晚了，但我自己也在研究这个问题。解决方法是在正在执行插入的调用过程中创建临时表，将范围标识从而不是触发器内部插入到临时表中，然后在插入完成后从临时表中读取标识值。

In procedure:

在程序中：

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

In instead of trigger:

而不是触发器：

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

You probably want to call it something other than #temp for safety sake (something long and random enough that no one else would be using it: #temp1234235234563785635).

为了安全起见，你可能想把它称为#temp以外的东西（长的和随机的东西，没有其他人会使用它：＃temp1234235234563785635）。

#6

Main Problem : Trigger and Entity framework both work in diffrent scope. The problem is, that if you generate new PK value in trigger, it is different scope. Thus this command returns zero rows and EF will throw exception.

主要问题：触发器和实体框架都在不同的范围内工作。问题是，如果在触发器中生成新的PK值，则它的范围是不同的。因此，此命令返回零行，EF将抛出异常。

The solution is to add the following SELECT statement at the end of your Trigger:

解决方案是在Trigger的末尾添加以下SELECT语句：

SELECT * FROM deleted UNION ALL
SELECT * FROM inserted;

in place of * you can mention all the column name including

代替*你可以提到所有的列名，包括

SELECT IDENT_CURRENT(‘tablename’) AS <IdentityColumnname>

#1