忽略SQL触发器执行中的结果集 [英] Ignore result sets in SQL trigger execution

问题描述

我有一个调用存储过程的SQL触发器。

I have an SQL trigger which calls a Stored Procedure. Something like this

Alter TRIGGER [dbo].[MyTrigger]
   ON       [dbo].[MyTable]
   AFTER    INSERT, UPDATE
AS 
BEGIN
    -- Some t-sql

    -- Call Stored Procedure
        exec MyProc
END

现在的问题是过程 MyProc 返回一个结果集，这是系统其他部分所必需的，但是我不希望通过触发器执行此过程时返回该结果集，因为只要我更新/插入<$ c，结果集就会返回$ c> MyTable 是在其上创建触发器的表，我不希望每次更新/插入时都设置该结果。

Now the issue is procedure MyProc returns a result set, which is required by other part of the system, But I don't want that result set to be returned when this procedure is executed via this trigger, because result set is returning whenever I'm updating/inserting in MyTable which is the table on which trigger has been created and I don't want that result set every time on update/insert.

那么这里有没有可能忽略触发器执行返回的结果集的解决方案？我考虑过一种将位参数传递给 MyProc 的解决方案，该参数指示是否通过触发器调用它，以及该位是否为1（表示通过触发器），我不会从过程中返回结果集，但是此解决方案会同时影响触发器和过程，因此，我希望仅通过触发器就可以完成某些操作，而无需完全触摸过程。

So Is there any possible solution of ignore the result set returned by trigger execution here? I have thought about a solution to pass a bit parameter to MyProc indicating whether it is being called via trigger or not and if that bit will be 1 (means called via trigger) I will not return result set from the procedure but this solution is affecting trigger as well as procedure both, I want something which can be done from trigger only without touching the procedure at all.

推荐答案

如果存储过程始终只返回一组，则可以禁止将其插入到临时表中。

if your stored procedure always returns only one set, you could suppress it my inserting into temporary table.

示例存储过程：

ALTER PROCEDURE [MyProc]
AS
BEGIN
  SELECT getdate() as today;
END

您的触发器应如下所示：

Your trigger should look like this:

 Alter TRIGGER [dbo].[MyTrigger]
   ON       [dbo].[MyTable]
   AFTER    INSERT, UPDATE
AS 
BEGIN
    -- Some t-sql

    -- Call Stored Procedure
    CREATE TABLE #myproc_tmp (
       today datetime
    )

    INSERT INTO #myproc_tmp
    Exec MyProc

END

重要说明-如果存储过程的返回结果集结构发生更改，则必须编辑触发器。此外，如果过程返回大量数据，则通过将其插入#myproc_tmp表，将会消耗额外的服务器资源。
最后，如果SP返回具有不同结构的多组数据，则INSERT INTO命令将引发错误。

Important note - if stored procedure's returned result set structure changes, you'll have to edit the trigger. Also if procedure returns large set of data, it'll consume extra server resources by inserting into #myproc_tmp table. And lastly, if SP returns multiple sets of data with different structures, the INSERT INTO command will throw an error.

这篇关于忽略SQL触发器执行中的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！