而不是 SQL Server 中的触发器丢失 SCOPE_IDENTITY? [英] Instead of trigger in SQL Server loses SCOPE_IDENTITY?

问题描述

我有一个表，我在其中创建了一个 INSTEAD OF 触发器来执行一些业务规则.

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

问题是，当我向这个表中插入数据时，SCOPE_IDENTITY() 返回一个 NULL 值，而不是实际插入的标识.

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

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

代码在创建此触发器之前工作.我在 C# 中使用 LINQ to SQL.我没有看到将 SCOPE_IDENTITY 更改为 @@IDENTITY 的方法.我该如何完成这项工作?

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?

推荐答案

使用 @@identity 而不是 scope_identity().

虽然 scope_identity() 返回当前作用域中最后创建的 id，@@identity 返回当前会话中最后创建的 id.

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

scope_identity() 函数通常推荐用于 @@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.

这篇关于而不是 SQL Server 中的触发器丢失 SCOPE_IDENTITY?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！