find and insert row to another table mysql trigger

Question

I have the following three tables in mysql database named "My_Company"

mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Id       | int(11)     | NO   | PRI | 0       |       |
| Emp_Name | varchar(20) | YES  |     | NULL    |       |
| Division | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc tools;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Division  | varchar(20) | NO   | PRI |         |       |
| Tool_No   | int(11)     | NO   | PRI | 0       |       |
| Tool_Name | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> desc employee_tools;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Id      | int(11)     | YES  |     | NULL    |       |
| Tool    | varchar(20) | YES  |     | NULL    |       |
| Status  | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

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

I need to insert the rows from table tools to table employee_tools when insert a new row on table employee.

Example, if i insert a new row to employees values as ('1','Michel','Network') then the the trigger should to find the tool_names of division from table tools and add the rows to employee_tools

mysql> insert into employee values('1','Michel','Network');
Query OK, 1 row affected (0.05 sec)

mysql> select * from employee;
+----+----------+----------+
| Id | Emp_Name | Division |
+----+----------+----------+
|  1 | Michel   | Network  |
+----+----------+----------+
1 row in set (0.00 sec)

mysql> select * from tools;
+----------+---------+--------------+
| Division | Tool_No | Tool_Name    |
+----------+---------+--------------+
| Network  |       1 | Crimper      |
| Network  |       2 | LAN Tester   |
| Network  |       3 | Sleaver      |
| Hardware |       1 | Screw drv    |
| Hardware |       2 | Power Tester |
| Hardware |       3 | Plyer        |
+----------+---------+--------------+
3 rows in set (0.00 sec)

mysql> select * from employee_tools;
+------+------------+------------+
| Id   | Tool       |Status      |
+------+------------+------------+
|    1 | Crimper    |Working     |
|    1 | LAN Tester |working     |
|    1 | Sleaver    |working     |
+------+------------+------------+
3 rows in set (0.00 sec)

the status will be updated manually like below...

+------+------------+------------+
| Id   | Tool       |Status      |
+------+------------+------------+
|    1 | Crimper    |Working     |
|    1 | LAN Tester |Not working |
|    1 | Sleaver    |Broken      |
+------+------------+------------+

Solution

Simple as that:

DROP TRIGGER IF EXISTS trg_emp_tools;
CREATE TRIGGER trg_emp_tools AFTER INSERT ON employee
FOR EACH ROW 
BEGIN
INSERT INTO employee_tools (Id, Tool, Status)
SELECT NEW.Id, tools.Tool_Name, 'Working'
FROM
tools
WHERE Division = NEW.Division;
END;

Comments

[dinesh.mic]

how to write a trigger like this for fire when update on employee.division

[fancyPants]

Usually it works like this on stackoverflow. You got a problem, you try something, you're stuck, you post what you've tried and where the problem is and what the desired result is -> we are happy to help. It's hard to tell what you even want to do, when you just write "for fire when update on employee.division".