MySQL find and insert row to another table mysql trigger

mysql 데이터베이스에 "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)

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

테이블 employee에 새 행을 삽입 할 때 테이블 도구의 행을 employee_tools 테이블에 삽입해야합니다.

예, 직원 값에 새 행을 ( '1', 'Michel', 'Network')로 삽입하면 then the the trigger should to find the tool_names of division from table tools 그리고 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)

상태는 아래와 같이 수동으로 업데이트됩니다.

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

 

해결 방법

 

다음과 같이 간단합니다.

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;

 

참조 페이지 https://stackoverflow.com/questions/15875953