Question

Write an AFTER Insert trigger for the following Employee table. Check Date of Birth and calculate age. Update AGE field with calculated value in Employee_Info table

• Employee (Emp_ID int, DOB date)

• Employee_Info (Emp_ID int, Fname char, Lname char, Age int)

• Set variable Current_Date to CURDATE()

• Substract DOB from Current_Date to find Age

Answer 1

Trigger is a object of the mysql database which will automatically execute once user will perform operation like insert, update, delete.

Steps to create trigger as per question:

1. We will create two variable to store current date and age

2. Then we will update the Employee_Info table by using WHERE clause.

3. For getting current date we will use CURDATE() pre-defined function to get the current date.

4. For getting the age we will use, DATE_FORMAT(), FROM_DAYS(), and DATEDIFF() functions.

----------------------------------------------------------------------------------------

CREATE TRIGGER after_dob_insert

AFTER INSERT

ON Employee FOR EACH ROW

BEGIN

DECLARE Current_date date;

DECLARE User_age int;

Current_date = Select CURDATE();

User_age = Select DATE_FORMAT(FROM_DAYS(DATEDIFF(Current_date,new.DOB)),'%Y')+0;

UPDATE Employee_Info SET Age = User_age WHERE Emp_ID = new.Emp_ID;

END //

-----------------------------------------------------------------------------------------------