How To Call a Stored Procedure From a Trigger in MySQL

Summary: in this tutorial, you will learn how to call a stored procedure from a trigger in MySQL.

MySQL allows you to call a stored procedure from a trigger by using the CALL statement. By doing this, you can reuse the same stored procedure in several triggers.

However, the trigger cannot call a stored procedure that has OUT or INOUT parameters or a stored procedure that uses dynamic SQL.

Let’s take a look at the following example.

Setting up a sample table

First, create a new table called accounts:

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
    accountId INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    amount DECIMAL(10 , 2 ) NOT NULL ,
    PRIMARY KEY (accountId),
    CHECK(amount >= 0) 
);
Code language: SQL (Structured Query Language) (sql)

Second, insert two rows into the accounts table:

INSERT INTO accounts(name, amount)
VALUES
    ('John Doe', 1000),
    ('Jane Bush', 500);
Code language: SQL (Structured Query Language) (sql)

Third, query data from the accounts table.

SELECT * FROM accounts;
Code language: SQL (Structured Query Language) (sql)

Create a stored procedure that withdraws from an account

This statement creates a stored procedure that withdraws an amount of money from an account:

DELIMITER $$

CREATE PROCEDURE Withdraw(
    fromAccountId INT, 
    withdrawAmount DEC(10,2)
)
BEGIN
    IF withdrawAmount <= 0 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'The withdrawal amount must be greater than zero';
    END IF;
    
    UPDATE accounts 
    SET amount = amount - withdrawAmount
    WHERE accountId = fromAccountId;
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

In this stored procedure, if the withdrawal amount is less than or equal to zero, it raises an error. Otherwise, it updates the amount of the account.

Create a stored procedure that checks the withdrawal

The following statement creates a stored procedure that checks the withdrawal from an account:

DELIMITER $$

CREATE PROCEDURE CheckWithdrawal(
    fromAccountId INT,
    withdrawAmount DEC(10,2)
)
BEGIN
    DECLARE balance DEC(10,2);
    DECLARE withdrawableAmount DEC(10,2);
    DECLARE message VARCHAR(255);

    -- get current balance of the account
    SELECT amount 
    INTO balance
    FROM accounts
    WHERE accountId = fromAccountId;
    
    -- Set minimum balance
    SET withdrawableAmount = balance - 25;

    IF withdrawAmount > withdrawableAmount THEN
        SET message = CONCAT('Insufficient amount, the maximum withdrawable is ', withdrawableAmount);
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = message;
    END IF;
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

In this stored procedure:

• First, get the current balance of the account.
• Second, set the withdrawable amount. The minimum balance of the account must be 25.
• Third, raise an error if the withdrawal amount is greater than the withdrawable amount.

Create a trigger that calls a stored procedure

The following statement creates a BEFORE UPDATE trigger that calls the stored procedure CheckWithdrawal:

DELIMITER $$

CREATE TRIGGER before_accounts_update
BEFORE UPDATE
ON accounts FOR EACH ROW
BEGIN
    CALL CheckWithdrawal (
        OLD.accountId, 
        OLD.amount - NEW.amount
    );
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

Testing the transactions

First, withdraw 400 from the account id 1:

CALL withdraw(1, 400);
Code language: SQL (Structured Query Language) (sql)

It worked.

Second, query data from the accounts table:

SELECT * FROM accounts
WHERE accountId = 1;
Code language: SQL (Structured Query Language) (sql)

The current balance of the account id 1 is 600.

Third, withdraw 600 from the account id 1:

CALL withdraw(1, 600);
Code language: SQL (Structured Query Language) (sql)

The stored procedure Withdraw executes an UPDATE statement that automatically invoked the trigger before_accounts_update.

The before_account_update trigger then calls the stored procedure CheckWithdrawal to check the withdrawal. It issues an error because the withdrawal amount causes the minimum balance zero, which is less than 25.

Error Code: 1644. Insufficient amount, the maximum withdrawable is 575.00Code language: JavaScript (javascript)

Fourth, withdraw 575 from the account id 1:

CALL withdraw(1, 575);
Code language: SQL (Structured Query Language) (sql)

Finally, verify the withdrawal by querying data from the accounts table:

SELECT * 
FROM accounts
WHERE accountId = 1;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to call a stored procedure from a trigger in MySQL.