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

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

user1499
1#
user1499 Published in April 26, 2018, 9:37 am

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO