Is it possible to get output result set from SQL Server stored procedure before the trigger executes?

Question

I have a main table Din_Test_Procedure:

CREATE TABLE Din_Test_Procedure(Emp_ID INT);

And an audit table for the table is:

CREATE TABLE Din_Test_Procedure_Audit(Emp_ID INT,LoopingValue BIGINT);

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

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

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.

Your trigger has **MAJOR** flaw in that you seem to assume it'll be called **once per row** - that is **not** the case. The trigger will fire **once per statement**, so if your `INSERT` statement that causes this trigger to fire inserts 25 rows, you'll get the trigger fired **once**, but then `Inserted` pseudo table will contain 25 rows. Which of those 25 rows will your code select here?? `SELECT @CurID = i.Emp_ID FROM inserted i;` - it's non-deterministic, you'll get **one arbitrary row** and you will be **ignoring all other rows**. You need to rewrite your trigger to take this into account! — marc_s, Jan 11 '18 at 08:33

Zohar Peled · Accepted Answer · 2018-01-11T07:51:00.243

3

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.

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

edited Jan 11 '18 at 07:51

answered Jan 11 '18 at 07:47

Zohar Peled

79,642
10
69
121

1

@ArijitMukherjee To the best of my knowledge, no. DML Triggers can't run separately from the statement that raised them. – Zohar Peled Jan 11 '18 at 08:22
1

@ArijitMukherjee It is possible to have the trigger update a flag in a different table and create a scheduled job to do whatever time-consuming work based on that flag, but that will disconnect the trigger from the action and is probably a bad idea. Also, your scheduled task will have to sample that other table quite often. – Zohar Peled Jan 11 '18 at 08:41

score 2 · Answer 2 · answered Jan 11 '18 at 07:19

You are generating a sequence of numbers in a loop. Then every value is inserted in a table. In other words, your code is handle the data row by agonizing row. You can try to optimize your code. For example, in your trigger use the following technique to insert the sequence:

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

    SELECT @CurID=i.Emp_ID FROM inserted i; 

    INSERT INTO SP_Test_Procedure_Audit VALUES(@CurID,@MIN)
    SELECT TOP (@Max-@Min+1) @CurID,  @Min-1+row_number() over(order by t1.number) as N
    FROM master..spt_values t1 
    CROSS JOIN master..spt_values t2


GO

You can also check this answer and this article of other alternatives to generate sequence.

If this is not enough, you can remove the trigger and scheduled the insertion with a delay if this is OK for your business needs.

Thanks for your reply friend. I do the loop for getting some delay, because it's just sample only. But actual query do some other process and it's getting delay. I don't want to improve my query performance, I want the result-set before the Trigger Executes. — DineshDB, Jan 11 '18 at 07:25
@DineshDB I see :-) Maybe you need to implement some scheduling than. You can create a task table and insert into it just the stored procedure that need to me executed (in your case this will be the code of your trigger). Then you can scheduled a job to check this table every X minutes and execute the code in it. — gotqn, Jan 11 '18 at 07:33

Is it possible to get output result set from SQL Server stored procedure before the trigger executes?

2 Answers2

Linked