2

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)
Improve this question

1 Answer 1

Reset to default
4

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 ;
Improve this answer
4
  • is it possible to insert into employee_tools (Id,Tool) values ('new.id', 'Tools.Tool_No & Tools.Tool_Name') ? Commented Apr 8, 2013 at 10:54
  • @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. Commented Apr 8, 2013 at 11:31
  • how to write a trigger like this for fire when update on employee.division Commented Apr 9, 2013 at 3:09
  • @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. Commented Apr 9, 2013 at 5:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.