是否可以使用触发器将值从一个表插入到另一个表？ [英] Is it possible to insert a value from one table to another on the basis of value using trigger?

问题描述

我有一个名为employee的表。
还有另外两个名为hrm的表，会计。
我想根据其部门将员工插入到人力资源管理和会计表中。

I've a table named employee. I've two other tables named hrm, accounting. I want to insert the employee into hrm and accounting table according to their departments.

CREATE TABLE employees (
employee_id INT(4) NOT NULL AUTO_INCREMENT,
employee_first_name VARCHAR(30) NOT NULL,
employee_last_name VARCHAR(30) NOT NULL,
employee_department VARCHAR(30) NOT NULL,
PRIMARY KEY (employee_id));

根据employee_department，我想将该雇员插入到特定表中。

according to the employee_department, I want to insert that employee to that particular table.

CREATE TABLE hrm (
employee_id INT(4),
employee_designation VARCHAR(20),
FOREIGN KEY (employee_id)
    REFERENCES employees (employee_id)
    ON DELETE CASCADE);

CREATE TABLE accounting (
employee_id INT(4),
employee_designation VARCHAR(20),
FOREIGN KEY (employee_id)
    REFERENCES employees (employee_id)
    ON DELETE CASCADE);

如果employee_department是hrm，我希望该雇员插入到hrm表中，我该怎么做通过mysql触发器还是存储过程？

if employee_department is hrm, I want that employee to get inserted into hrm table, how can i do it through mysql trigger or stored procedure?

推荐答案

尝试类似这样的东西：

CREATE DEFINER = CURRENT_USER TRIGGER `employees_AFTER_INSERT` AFTER INSERT ON `employees` 
FOR EACH ROW
BEGIN

  IF NEW.employee_department = 'hrm' THEN
      INSERT INTO hrm 
        (employee_id, employee_designation) 
      VALUES
        (NEW.employee_id, /*whatever you add here*/);
  END IF;

END

我将解释一些关键点：

• 在 AFTER INSERT 触发器中，您可以使用 NEW获取插入值。因此，这里： NEW.employee_department 等于新雇员插入的 employee_department 值，与<$ c $相同c> NEW.employee_id （对于删除后触发器，您可以使用 OLD ，对于更新后，您需要 NEW 作为更新值，而 OLD


• 在MySQL触发器中，您可以添加条件：在这里，我检查是否新的 employee_department 员工等于 hrm：如果是，然后在您的hrm表中插入，否则触发器不执行任何操作


• 如果您要对 accounting执行相同操作，则逻辑将一样

• In an AFTER INSERT trigger, you can get the inserted valued using NEW. So here : NEW.employee_department is equal to the inserted employee_department value of the new employee, same for NEW.employee_id (for AFTER DELETE trigger you can use OLD and for AFTER UPDATE you have NEW for the updated value and OLD for the value before the update)
• In a MySQL trigger, you can add condition : here I check if the new employee_department of the new employee is equal to "hrm" : if YES THEN do an insert in your hrm table, ELSE the trigger do nothing
• If you want to do the same for "accounting", the logic will be the same

这篇关于是否可以使用触发器将值从一个表插入到另一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！