Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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 ;
share|improve this answer
    
its working... Thank you... –  dinesh.mic Apr 8 '13 at 10:42
    
is it possible to insert into employee_tools (Id,Tool) values ('new.id', 'Tools.Tool_No & Tools.Tool_Name') ? –  dinesh.mic Apr 8 '13 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. –  Gord Thompson Apr 8 '13 at 11:31
    
how to write a trigger like this for fire when update on employee.division –  dinesh.mic Apr 9 '13 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. –  Gord Thompson Apr 9 '13 at 5:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.