如何获取插入的表标识而不是插入触发器？

debugcn 发表于 Dev

佳美

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

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。

任何帮助，将不胜感激。

佐哈尔·皮莱德（Zohar Peled）

好吧，你在那里泡菜了。
一方面，您需要instead of insert表上的触发器，但另一方面，您想让该触发器生成的标识返回到激活它的存储过程。

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

寻找某种方法来消除对触发器的需求。
这是我最好的建议。
将存储过程分为两部分：一部分将执行所有操作，直到insert into语句（包括它）为止，从而激活instead of insert触发器，另一部分将执行触发器之后所需的所有操作。这样，您可以scope_identity()在instead of insert触发器内部使用并将其返回值作为参数发送到第二个存储过程。
注意：这种设计意味着您必须一个一个地插入记录。如果您尝试插入多条记录，scope_identity()则只会返回触发器插入的最后一行的标识。
寻找某种在存储过程和触发器之间传递数据的方法。由于触发器不能排除或返回参数，因此您将不得不使用临时表或常规表。此建议的解决方案仅在万不得已时才建议使用，因为这会使您的代码复杂化，并且还可能导致一些性能问题。另外，您将必须找到一种方法来保持存储过程的执行，直到触发器代替触发器完成工作为止。我可以为您提供一些有关如何在过程和触发器之间共享数据的指示，但我确实建议不要选择此解决方案。