时间戳

首页 > 解决方案 > 在插入表之前创建触发器

mysql - 在插入表之前创建触发器

问题描述

我在插入员工表以计算其年龄之前创建了一个触发器，但尝试插入员工表会返回以下错误错误代码：1442。无法更新存储函数/触发器中的表“员工”，因为它已被使用by 调用这个存储的函数/触发器的语句

CREATE TABLE IF NOT EXISTS person (
  ssn INT(20) NOT NULL,  
  name_person VARCHAR(200) NOT NULL,
  birth_date DATE NOT NULL,   
  PRIMARY KEY (ssn))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS employee (
  ssn_employee INT(20) NOT NULL,
  job_title VARCHAR(100) NOT NULL,
  age INT(10) DEFAULT NULL,
  PRIMARY KEY(ssn_employee),  
  FOREIGN KEY (ssn_employee)
  REFERENCES person (ssn)
  ON UPDATE CASCADE)
ENGINE = InnoDB;    

DELIMITER $$
CREATE TRIGGER employeeAge
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN   
    DECLARE dob DATE;
    DECLARE ssn_employee1 int;
    SELECT new.ssn_employee INTO ssn_employee1;
    SELECT person.birth_date into dob FROM person WHERE ssn = ssn_employee1;
    UPDATE employee
    SET new.age = DATEDIFF(dob, GETDATE());     
END
DELIMITER;$$

标签： mysqlsqldatabasetriggers

解决方案

感谢你们。我是初学者，提示已澄清。我这样做了：

```lang_sql
DELIMITER $$
CREATE TRIGGER employeeAge
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN   
    DECLARE dob DATE;
    DECLARE ssn_employee1 int;
    SELECT new.ssn_employee INTO ssn_employee1;
    SELECT person.birth_date into dob FROM person WHERE ssn = ssn_employee1;   
    SET new.age = TIMESTAMPDIFF(YEAR, dob, NOW());    
END;
DELIMITER $$

推荐阅读