如何获取表标识而不是插入触发器？ [英] How to get a table identity inserted by instead of insert trigger?

问题描述

我有一个描述如下的问题：我有一个表，而不是一个插入触发器：

I have a problem described as follows: I have a table with one instead of insert trigger:

create table TMessage (ID int identity(1,1), dscp varchar(50))
GO
Alter trigger tr_tmessage on tmessage
instead of insert
as

--Set NoCount On

  insert into tmessage
  select dscp from inserted
GO
Alter proc P1
As

--Set NoCount On

insert into tmessage
(dscp)
values('some data')

Select SCOPE_IDENTITY()

GO

当我执行P1时，它为SCOPE_IDENTITY（）返回Null而不是表的标识。我什至在proc的insert语句中尝试了Output子句。但是在这种情况下，通过在Output子句中插入来填充的输出表Identity字段再次为0。

When I execute P1 it returns Null for SCOPE_IDENTITY() instead of the identity of the table. I even tried Output clause in the insert statement in the proc. but again the output table Identity field that gets filled from inserted in the Output clause is 0 in this case.

我们将不胜感激。

推荐答案

好吧，你在那里腌了很多。

一方面，你需要在表上使用代替插入触发器，但是从另一方面来说，您想让此触发器生成的标识返回到激活它的存储过程。

Well, you've got yourself quite a pickle there.
From the one hand, you need the instead of insert trigger on your table, but from the other hand, you want to get the identity that this trigger generates back to the stored procedure that activated it.

由于无法在触发器之间发送参数，因此您必须做以下三件事之一：

Since there is no way to send parameters to and from triggers, you will have to do one of 3 things:

1. 找到某种方法来消除对此的需要，而不是触发。
   
   这是我的最佳建议。

将存储过程分为两部分：一部分将完成所有操作，直到插入语句（包括它）为止，这样就激活了而不是insert 触发器，另一部分将在触发器之后执行所有需要的操作。这样，您可以在内使用 scope_identity（）而不是insert 触发器，并将其返回值发送到第二个存储的

注意：这种设计意味着您必须一个一个地插入记录。如果您尝试插入多个记录，则 scope_identity（）将仅返回触发器插入的最后一行的标识。

Break your stored procedure to 2 parts: One part will do everything until the insert into statement (including it), thus activating the instead of insert trigger, and the other part that will do all operations needed after the trigger. this way you can use the scope_identity() inside the instead of insert trigger and send it's return value to the second stored procedure as a parameter.
Note: this design means you have to insert records one by one. should you try to insert more then one record, scope_identity() will only return the identity of the last row inserted by the trigger.

这篇关于如何获取表标识而不是插入触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！