Trigger with if condition in mysql
In this article, we will see how to structure the IF condition in MySQL.
Triggers are named database objects linked with a table that execute automatically when a particular event occurs on the table. Let us look at adding if conditions within trigger in MySQL by an example, but before moving ahead, we will create sample tables employee_details and special_bonus_employees.
READ MORE ABOUT TRIGGERS: What are triggers in MySQL
# create the table employee_details CREATE TABLE employee_details ( emp_id INT , emp_name VARCHAR(255), emp_designation VARCHAR(255), emp_salary INT ); # create the table special_bonus_employees CREATE TABLE special_bonus_employees ( id INT , emp_name VARCHAR(255) );
Let us now create a trigger to insert a row to the special_bonus_employees table after the insert happens on employee_details but only if the emp_designation is Senior Manager. Observe and execute the below MySQL code for the solution.
delimiter // CREATE TRIGGER insert_after_employee_details AFTER INSERT ON employee_details FOR EACH ROW BEGIN IF (NEW.emp_designation = 'Senior Manager') THEN INSERT INTO special_bonus_employees ( emp_id,emp_name ) VALUES ( NEW.emp_id, NEW.emp_name); END IF; END; //
- Here we are creating a trigger named insert_after_employee_details on the table employee_details. It is AFTER INSERT Trigger.
- The trigger will insert a row into the table special_bonus_employees after the insert is made to the employee_details table. The IF condition checks if the emp_designation = Senior Manager. If yes, only then is the insertion made to the special_bonus_employees table.
Testing the Trigger:-
Let us now test our trigger by inserting rows into the table employee_details followed by selecting * on the same table.
INSERT INTO employee_details (emp_id,emp_name,emp_designation,emp_salary) VALUES(1,"Veronica","Assistant Manager",50000), (2,"Richa","Assistant Manager",50000), (3,"George","Assistant Sales Executive",12000 ), (4,"William","Assistant Sales Executive",12000 ), (5,"Pamela","Senior Manager",72000 ), (6,"Hally","Accountant",22000 ); SELECT * FROM employee_details;
Rows are inserted successfully into employee_details. We will now verify if the trigger made the correct entry into the special_bonus_employees table.
SELECT * FROM special_bonus_employees;
The output in image_4 shows that only one row is inserted into the table special_bonus_employees because only one employee named Pamela had the designation -Senior Manager.
- What are indexes in MySQL
- Create trigger on delete
- MySQL trigger example before insert
- MySQL trigger example after insert
- MySQL trigger example before update
- MySQL trigger example after update
We hope this article helped you with structuring the IF condition in MySQL triggers. Good Luck!!!