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.
Also read: Create Multiple Triggers For The Same Trigger Event And Action Time
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
);Code language: SQL (Structured Query Language) (sql)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);Code language: SQL (Structured Query Language) (sql)Let’s display the table data.
SELECT * FROM accounts;Code language: SQL (Structured Query Language) (sql)
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
//Code language: SQL (Structured Query Language) (sql)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
//Code language: SQL (Structured Query Language) (sql)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);
ENDCode language: SQL (Structured Query Language) (sql)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.
CALL withdraw(1,100);Code language: SQL (Structured Query Language) (sql)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;Code language: SQL (Structured Query Language) (sql)
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.
CALL withdraw(2,-200);Code language: SQL (Structured Query Language) (sql)
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.
CALL withdraw(2,500);Code language: SQL (Structured Query Language) (sql)
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
Conclusion
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.