是否可以在执行触发器之前从SQL Server存储过程获取输出结果集？ [英] Is it possible to get output result set from SQL Server stored procedure before the trigger executes?

问题描述

我有一个主表 Din_Test_Procedure ：

CREATE TABLE Din_Test_Procedure(Emp_ID INT);

该表的审核表为：

CREATE TABLE Din_Test_Procedure_Audit(Emp_ID INT,LoopingValue BIGINT);

我为表编写了一个触发器，当 INSERT 发生，一百万条记录插入到审核表中：

And I write a trigger for the table, when an INSERT occurs, 1 million records insert into the audit table:

CREATE TRIGGER TrgAfterInsert_SPTest 
ON [dbo].[Din_Test_Procedure] 
FOR INSERT
AS
    DECLARE @CurID INT, @MIN INT = 1, @MAX INT = 1000000

    SELECT @CurID = i.Emp_ID 
    FROM inserted i;    

    WHILE @MIN <= @MAX
    BEGIN
        INSERT INTO Din_Test_Procedure_Audit 
        VALUES(@CurID, @MIN)

        SET @MIN = @MIN + 1
    END
GO

我还创建了一个存储过程，并使用 OUTPUT 参数将记录插入主表。

And also I create a stored procedure with an OUTPUT parameter to insert record into the main table.

CREATE PROCEDURE Din_Insert_SPTest 
    @ID INT, @ErrCode INT OUTPUT
AS
BEGIN
    INSERT INTO Din_Test_Procedure 
        SELECT @ID

    SELECT @ErrCode = 0
END

当我执行该过程时，它显示<$ c $触发执行完成后，c> OUTPUT 。

When I execute the procedure, it shows the OUTPUT after the trigger execution completes. It took some time delay to give the output.

DECLARE @ErrCode INT
EXEC Din_Insert_SPTest 1,@ErrCode OUTPUT
SELECT @ErrCode

一旦主表插入完成，是否有可能获得输出，

Is it possible to get the output once the main table insertion completes, which means get the output before the trigger process completes.

推荐答案

否。

SQL Server将控制权返回给

这意味着 SELECT @ErrCode = 0 仅在之后触发器已执行，当然，您只能从存储过程 完成后才能返回结果。

No.
SQL Server will return control to the stored procedure only after the trigger has completed it's run.
This means that SELECT @ErrCode = 0 will only happen after the trigger was executed, and of course, you can only get the results back from the stored procedure after it's completed it's run.

原因是触发它的触发器和语句在隐式事务内执行。
有关更多信息，请参见Microsoft的 DML触发器文档

The reason for this is that the trigger and statement that fires it are executed inside an implicit transaction. For more information, see Microsoft's DML Triggers documentation

这篇关于是否可以在执行触发器之前从SQL Server存储过程获取输出结果集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！