find and insert row to another table using 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 | |
+-------+-------------+------+-----+---------+-------+
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_No & 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 |
+------+--------------+
| 1 | 1_Crimper |
| 1 | 2_LAN Tester |
| 1 | 3_Sleaver |
+------+--------------+
3 rows in set (0.00 sec)

Answer 1

This should do the trick for you:

DELIMITER $$
DROP TRIGGER IF EXISTS `employee_INSERT` $$
CREATE TRIGGER `employee_INSERT` 
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
 INSERT INTO employee_tools (Id, Tool)
 SELECT new.Id, tools.Tool_Name 
 FROM tools
 WHERE tools.Division = new.Division;
END $$
DELIMITER ;

• dinesh.mic: is it possible to insert into employee_tools (Id,Tool) values ('new.id', 'Tools.Tool_No & Tools.Tool_Name') ?
• Gord Thompson: @dinesh.mic Yes, that would be possible, but it would violate the principle of Normalization. If you store tools.Tool_No in employee_tools then you shouldn't also store tools.Tool_Name in employee_tools because you already have that information available from the tools table.
• dinesh.mic: how to write a trigger like this for fire when update on employee.division
• Gord Thompson: @dinesh.mic The current trigger fires AFTER INSERT ON employee. You'll need to create a second trigger that fires AFTER UPDATE ON employee. For more details, look here.