find and insert row to another table mysql trigger
find, insert, mysql, triggers
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;
Problem
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 | +------+------------+------------+ ```