Skip to main content
mohank0008
mohank0008
September 15, 2022
Solved

Regarding the MariaDB transactions

  • September 15, 2022
  • 1 reply
  • 0 views

In My Practice Application,

I have an accounts table that contains the details of the account holder like the name, balance, etc.

Here, I am writing a stored procedure to transfer an amount from one account to another account

          in the Transfer Amount stored procedure, 2 steps need to be included in a transaction, 

                            1)  subtracting amount from the sender account.

                            2) Adding amount to the receiver account 

Best answer by jammulas0001

Hi mohan, can you please try the below code. Here I have used customer and accounts table for example. If there is an error signal, customer table will be printed, else account table will be printed.

DELIMITER $$
CREATE DEFINER=dbadmin@% PROCEDURE transactionCode()
BEGIN
    DECLARE _rollback BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;
        
    START TRANSACTION;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "This above executed SQL statements will be rollbacked";
    IF _rollback 
    THEN
        ROLLBACK;
        SELECT * from Customer;
    ELSE
        COMMIT;
        SELECT * from accounts;
    END IF;
END$$
DELIMITER ;

1 reply

jammulas0001
jammulas0001Answer
September 16, 2022

Hi mohan, can you please try the below code. Here I have used customer and accounts table for example. If there is an error signal, customer table will be printed, else account table will be printed.

DELIMITER $$
CREATE DEFINER=dbadmin@% PROCEDURE transactionCode()
BEGIN
    DECLARE _rollback BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;
        
    START TRANSACTION;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "This above executed SQL statements will be rollbacked";
    IF _rollback 
    THEN
        ROLLBACK;
        SELECT * from Customer;
    ELSE
        COMMIT;
        SELECT * from accounts;
    END IF;
END$$
DELIMITER ;

    Terms & ConditionsAccessibility statement