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

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

DineshDB
1#
DineshDB Published in 2018-01-11 06:48:33Z

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.

Zohar Peled
2#
Zohar Peled Reply to 2018-01-11 07:51:00Z

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

gotqn
3#
gotqn Reply to 2018-01-11 07:19:20Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.308656 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO