Create trigger before insert in table

Question

I created a trigger before inserting into the employee table to calculate its age, but trying to insert into the employee table returns the following erro Error Code: 1442. Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

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;$$

Seems to be pretty clear. You cannot do Update in the middle of Insert into the same table. — PM 77-1
– PM 77-1, Commented Jul 22, 2021 at 16:53

SteveSTS · Accepted Answer · 2021-07-22 17:36:16Z

0

Thank you guys. I'm a beginner and the tips clarified. I did this:

```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 $$

answered Jul 22, 2021 at 17:36

SteveSTS

33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create trigger before insert in table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related