而不是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.
通常建议在@@identity
字段上使用scope_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屋!