CODEWITHSUNDEEP
sql-servertransactions
user7143384
user7143384

Reputation: 17

sql transaction does not log when failed but the transaction still goes through

My program registers everything correctly when all the requirements for the transaction are fulfilled and logs the transaction information.

But when faulty information is given the information is not logged but the transaction still goes through and I am not sure what I did wrong.

First up is the stored procedure the logs the transaction info

/****** Object:  StoredProcedure [dbo].[LogTransactionAttempt]    Script Date: 8/1/2017 9:57:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LogTransactionAttempt]
    (@ToUserAccountId int,
     @FromUserAccountId int,
     @TransferAmount money,
     @CreditorWithdrawl nvarchar(50),
     @TransferCode numeric(18,0))
AS
BEGIN
    INSERT INTO Transactions (FromUserAccountId, ToUserAccountId, TransferAmount, 
                              CreditorWithdrawal, TransferCode, TransactionDateTime)
    VALUES (@FromUserAccountId, @ToUserAccountId, @TransferAmount, 
            @CreditorWithdrawl, @TransferCode, GETDATE())

    RETURN @@IDENTITY
END

Here is the overall procedure

/****** Object:  StoredProcedure [dbo].[performTransaction]    Script Date: 8/1/2017 9:57:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[performTransaction]
    (@ToUserAccountId int,
     @FromUserAccountId int,
     @TransferAmount money,
     @CreditorWithdrawl nvarchar(50),
     @TransferCode numeric(18,0))
AS
BEGIN
    DECLARE @FromUserAccountTest INT

    SELECT @FromUserAccountTest = (SELECT Count(*) 
                                   FROM dbo.UserAccount
                                   WHERE UserAccountId = @FromUserAccountID)

    IF @FromUserAccountTest <> 1 
    BEGIN
        RAISERROR('From Account Does Not Exist', 10, 1)
    END

    DECLARE @ToUserAccountTest INT

    SELECT @ToUserAccountTest = (SELECT Count(*) 
                                 FROM dbo.UserAccount   
                                 WHERE UserAccountId = @ToUserAccountID)

    IF @ToUserAccountTest <> 1 
    BEGIN
        RAISERROR('To Account Does Not Exist', 10, 2)
    END

    -- At this point we can log the transaction
    DECLARE @TransactionID INT 

    EXEC @TransactionID = dbo.LogTransactionAttempt  @ToUserAccountId, @FromUserAccountId, @TransferAmount, @CreditorWithdrawl, @TransferCode

    -- Check to see if sufficient balance
    DECLARE @FromAccountBalance  MONEY

    SELECT @FromAccountBalance = (SELECT CreditBalance
                                  FROM dbo.UserAccount
                                  WHERE UserAccountID = @FromUserAccountID)

    IF (@FromAccountBalance < @TransferAmount)     
    BEGIN
        RAISERROR('Insufficient Balance', 10, 3)
    END

    -- Now we can start the transaction
    BEGIN TRANSACTION 
        UPDATE UserAccount
        SET CreditBalance = CreditBalance - @TransferAmount
        WHERE UserAccountId = @FromUserAccountID

        IF (@@ERROR <> 0)
        BEGIN
            ROLLBACK TRANSACTION   /* if errors - rollback transaction */
            RETURN 
        END

        UPDATE UserAccount
        SET CreditBalance = CreditBalance + @TransferAmount
        WHERE UserAccountID = @ToUserAccountID

        IF (@@ERROR <> 0)
        BEGIN
            ROLLBACK TRANSACTION   /*if errors - rollback transaction */
            RETURN 
        END

        UPDATE dbo.Transactions
        SET Successful = 1
        WHERE TransactionID = @TransactionID 

        IF (@@ERROR <> 0)
        BEGIN
            ROLLBACK TRANSACTION   /* if errors - rollback transaction */
            RETURN 
        END

        -- Transaction
        COMMIT TRANSACTION

END -- Procedure

Like I said everything works perfect when the inputs are correct but if there is a bad input say a non existent account or lack of funds, it doesn't log the failure but the transaction still goes through

Edit tried switching to 16 from 10 no effect

example of a correct transaction moving $100 from account 11 with a balance of $100 to account 22 with no balance

USE [Transaction]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[performTransaction]
    @ToUserAccountId = 22,
    @FromUserAccountId = 11,
    @TransferAmount = 100,
    @CreditorWithdrawl = N'credit',
    @TransferCode = 1

SELECT  'Return Value' = @return_value

GO

that correct entry returns

1017    22  11  100.0000    credit  1   2017-08-02 10:19:23.110 yes

in the transaction log

entering faulty information such as a transfering $150 from an account with a balance of only $100 raises the error codes properly but does not cancel the transaction even though the requested money is not all there

USE [Transaction]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[performTransaction]
    @ToUserAccountId = 22,
    @FromUserAccountId = 11,
    @TransferAmount = 150,
    @CreditorWithdrawl = N'credit',
    @TransferCode = 2

SELECT  'Return Value' = @return_value

GO

returns the following in the messages

(1 row(s) affected)
Msg 50000, Level 16, State 3, Procedure performTransaction, Line 72 [Batch Start Line 2]
Insufficient Balance

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

So there errors are seen but the faulty transaction is not logged, but the transaction still goes through resulting in a negative balance

USE [Transaction]
GO
/****** Object:  StoredProcedure [dbo].[LogTransactionAttempt]    Script Date: 8/2/2017 9:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[LogTransactionAttempt]


(


  @ToUserAccountId int,
  @FromUserAccountId int,
  @TransferAmount money,
  @TransferCode numeric(18,0)
)

 AS

 BEGIN

  INSERT INTO Transactions

  (

    UserAccountId,
    TransferAmount, 
    CreditorWithdrawal,
    TransferCode,
    TransactionDateTime
  )

  VALUES 

  (
    @FromUserAccountId,
    @TransferAmount, 
    'Withdrawl',
    @TransferCode,
    GETDATE()
  )
  ,
  (
    @ToUserAccountId,
    @TransferAmount, 
    'Credit',
    @TransferCode,
    GETDATE()
  )



  RETURN @@IDENTITY



  END

Upvotes: 1

Views: 459

Answers (1)

Xingzhou Liu
Xingzhou Liu

Reputation: 1559

Raiserror does not set @@Error unless your severity level is 16 or higher. Messages less than 16 are considered informational, not errors. Try the following code to verify:

RAISERROR('Insufficient Balance', 10, 3)
select @@Error; -- will be 0
RAISERROR('Insufficient Balance', 16, 3)
select @@Error; -- will be 50000

Also, note that RAISERROR will NEVER break execution, unless you do something like raise a fatal error that causes the connection to end immediately (you'll need sysadmin privileges). So with exception of where you use an explicit "return", expect all code after RAISERROR to execute.

Read this for more about RAISERROR and breaking: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/483d6be7-6518-41c9-8138-aa1412ae8252/raiseerror-to-stop-execution?forum=transactsql.

In addition you need to 

DECLARE @errcode INT 

after 

DECLARE @FromUserAccountTest INT;

the add 

select @errcode = @@ERROR 

immediately after each raiserror

Reason is any successful event will reset @@ERROR to 0 - so all of the following resets your @@ERROR variable to 0;

SELECT @FromUserAccountTest = (SELECT Count(*) 
                               FROM dbo.UserAccount
                               WHERE UserAccountId = @FromUserAccountID)

SELECT @FromUserAccountTest = (SELECT Count(*) 
                               FROM dbo.UserAccount
                               WHERE UserAccountId = @FromUserAccountID)


EXEC @TransactionID = dbo.LogTransactionAttempt  @ToUserAccountId, @FromUserAccountId, @TransferAmount, @CreditorWithdrawl, @TransferCode

    UPDATE UserAccount
    SET CreditBalance = CreditBalance - @TransferAmount
    WHERE UserAccountId = @FromUserAccountID

Then finally change your first occurrence of

IF @@ERROR <> 0

to 

IF @@errcode <> 0

leave the second occurrence as

IF @@ERROR <> 0

because it's testing the result of the update statement immediately preceding.

Upvotes: 2

Related Questions