CODEWITHSUNDEEP
sql-serverstored-procedurestriggers
Steven
Steven

Reputation: 1

Rollback all transactions in a trigger

I have a requirement to call a procedure in a trigger which will call another few more transnational procedures. How to make sure all transactions are rollback if any of the procedure is failed. Thanks 

CREATE PROCEDURE dbo.PROC2(@pi_row INT)
AS
 BEGIN
  UPDATE TABLE abc SET col1 = @pi_row
 END 
GO

CREATE PROCEDURE dbo.PROC1(@pi_row INT)
AS
 BEGIN
  UPDATE TABLE xyz SET col1 = @pi_row
  EXEC dbo.PROC1 @pi_row
 END 
GO

CREATE TRIGGER test_insert ON xyz
    FOR INSERT
AS
  BEGIN
     DECLARE @pi_row int;

     SELECT @pi_row = rownumber
     FROM deleted;

     EXEC dbo.proc1 @pi_row;
  END; 
GO

Upvotes: 0

Views: 273

Answers (1)

Mihir Shah
Mihir Shah

Reputation: 988

this may helpful :-

CREATE PROCEDURE dbo.PROC2(@pi_row INT)
AS
BEGIN
    UPDATE TABLE abc SET col1 = @pi_row
END 
GO

CREATE PROCEDURE dbo.PROC1(@pi_row INT)
AS
BEGIN

    Declare @TransName Varchar(100) = 'PIRowUpdation'

    Begin Tran @TransName

    UPDATE TABLE xyz SET col1 = @pi_row
    If @@Error <> 0 Goto Error

    EXEC dbo.PROC2 @pi_row
    If @@Error <> 0 Goto Error

    Commit Tran @TransName
    Goto LevelEnd

Error:
    Rollback Tran @TransName

LevelEnd:


END 
GO

Upvotes: 2

Related Questions