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)
);

Action Output:-

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; // 

Explanation:-

• 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.

Action Output:-

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;

Output:-

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;

Output:-

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.

READ MORE:

• 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!!!