In this tutorial, we will learn how to call a stored procedure from a trigger. This is going to be a very interesting tutorial because we will see two things in a single place, Triggers and Stored Procedures. Read the tutorial carefully so that you won’t miss any part of it and do try yourself as well.
Introduction and Prerequisites
Before we move, let’s understand what we are going to achieve. As the title suggests, we will be creating a trigger and from that trigger, we will call a stored procedure. Sounds difficult, right? But it is pretty easy.
Our method –
- First, we will create a stored procedure, say “withdraw” which will deduct the entered amount from the current available amount. In simple terms, we will update the table row of a given customer.
- Second, we will create the BEFORE UPDATE trigger. Whenever the row is updated by the first procedure, this trigger will get activated.
- Third, we will create another procedure, say “checkWithdraw” which will check if the balance entered to withdraw is correct or not. We will call this procedure from the trigger that we create in the above step.
- That’s it. The “checkWithdraw” procedure will be called from the BEFORE UPDATE trigger.
Create a Table
Let’s create a table first to store the id, name, and balance of customers.
CREATE TABLE accounts id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), balance FLOAT );
Insert data into it.
INSERT INTO accounts(name,balance)VALUES ("Jenos Adams",1400),("Peter Parker",400), ("James Anderson",100),("May Bailey",540),("Earen Barker",300);
Let’s display the table data.
SELECT * FROM accounts;
Create Stored Procedure To Withdraw Money
Now, we will create a stored procedure that will deduct the entered amount from the current amount in the table.
The procedure will have two parameters. First is the id of the customer and the second is the amount. We will check if the amount entered is greater than 0 and if it is then we will update the account balance by subtracting the entered amount from the current amount.
CREATE PROCEDURE withdraw (uid INT, wamount FLOAT) BEGIN IF wamount <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT="amount should be greater than 0"; END IF; UPDATE accounts SET balance= balance-wamount WHERE id= uid; END //
The “withdraw” procedure is created. Let’s create a “checkwithdraw” procedure.
Create Stored Procedure To Check Withdraw Money
In the “checkwithdraw” stored procedure, we will check if the entered amount is less than the current available amount. If the entered amount is greater than the available balance, we will show an error. Else, we will proceed to the operation without any error.
Note that, the “checkwithdraw” stored procedure will be called from a trigger body.
CREATE PROCEDURE checkwithdraw (uid INT,wamount FLOAT) BEGIN DECLARE nbalance FLOAT; SELECT balance INTO nbalance FROM accounts WHERE id= uid; IF wamount > nbalance THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT= "Insufficient amount to withdraw"; END IF; END //
The “checkwithdraw” procedure is created successfully. Let’s create a trigger that will call the checkwithdraw procedure.
Create a Trigger To Call the “Checkwithdraw” Procedure
Here, we will create the BEFORE UPDATE trigger that calls the “checkwithdraw” procedure. We will pass two parameters to the procedure- customer id and the amount.
CREATE TRIGGER account_validate BEFORE UPDATE ON accounts FOR EACH ROW BEGIN CALL checkwithdraw(OLD.id, OLD.balance-NEW.balance); END
The trigger is created successfully. Whenever the “withdraw” procedure is called, a new row will get updated. When an update query is fired, the trigger will execute and the trigger body will call the “checkwithdraw” procedure. This way everything works. Let’s test it now.
Testing the Transactions
We have created procedures and the trigger with utmost precautions. We should get the expected output as soon as we call the “withdraw” procedure. Let’s try.
We will deduct the amount from a customer of id 1.
If the query executes successfully, the amount of the customer of id 1 should be deducted by 100 Rs. Let’s check the table.
SELECT * FROM accounts;
You can see, we have got the expected output. The previous amount of the customer of id 1 was Rs 1400. Now, it’s Rs 1300 after withdrawing Rs 100.
Let’s enter a negative amount to check what output we get.
As you can see, we have got an error- “amount should be greater than 0”.
Let’s test the transaction by deducting the amount greater than the available balance.
As you can see, we have got an error- “Insufficient amount to withdraw” because the customer of id 2 has a balance of Rs 400 only so we cannot withdraw 500Rs.
Flow of Transaction
If you are still confused about what’s going on here then let me explain it.
- First, we call the “withdraw” procedure with two parameters – customer id and amount to withdraw.
- The “withdraw” procedure checks if the amount is negative. If the amount is negative, it shows an error. Else, it proceeds where we perform an update operation. We subtract the given amount from the available balance.
- When the update query is fired, the trigger is executed just before the update takes place in the table.
- Second, the trigger executes, and inside the trigger, we call the “checkwithdraw” procedure and pass two parameters to it- id and the entered amount.
- Third, the “checkwithdraw” procedure checks if the entered amount is not greater than the available balance. If the entered amount is greater than the available balance, it shows an error and operation stops without update taking place in the table.
- If the entered amount is not greater than the available balance, it proceeds to the update operation and the new balance is updated in the table
I hope you have understood the tutorial on how to call a stored procedure from a trigger. If you want detailed information on each type of trigger, you can check them on our website using the search box. You can also read our tutorial on how to call a stored procedure from a python program. Stay in touch with mysqlcode.com for more exciting tutorials.