Set age from date of birth in another table

Question

I’m trying to create a Trigger to set the table age attribute funcionario:

```sql

   CREATE TABLE IF NOT EXISTS `funcionario`(
      `cpf_funcionario` INT(11) NOT NULL,      
      `idade` SMALLINT(10) DEFAULT NULL,
      PRIMARY KEY(`cpf_funcionario`),  
      FOREIGN KEY (`cpf_funcionario`)
      REFERENCES `pessoa` (`cpf`)
      ON UPDATE CASCADE
      ON DELETE RESTRICT)
    ENGINE = InnoDB;
```

Age would be picked up from the person table:

   ```sql  
    CREATE TABLE IF NOT EXISTS `pessoa` (
      `cpf` INT(11) NOT NULL,      
      `nome` VARCHAR(100) NOT NULL,
      `data_Nascimento` DATE NOT NULL,      
      `cep` VARCHAR(9) NULL DEFAULT NULL,
      `telefone` VARCHAR(100) NULL DEFAULT NULL,
      PRIMARY KEY (`cpf`))
    ENGINE = InnoDB;

    DELIMITER $$
    CREATE TRIGGER CalcIdade
    AFTER INSERT ON funcionario
    FOR EACH ROW
    BEGIN
      set NEW.idade = (YEAR(CURDATE()) - YEAR(NEW.data_Nascimento) - (RIGHT(CURDATE(),5)) < RIGHT(NEW.data_Nascimento,5)) AS idade FROM pessoa);
    END$$
    
    DELIMITER $$
```   

But I didn’t succeed.

EDIT:

 ``` sql
DELIMITER $$

CREATE VIEW IdadeFuncionarios AS
SELECT TIMESTAMPDIFF(YEAR, P.`data_Nascimento`, NOW()) 
FROM funcionario F, pessoa P
INNER JOIN `pessoa`
ON (F.`cpf_funcionario` = P.`cpf`);

DELIMITER $$
```

It didn’t work either or I did something wrong?

Answer 1

CREATE VIEW IdadeFuncionarios AS
  SELECT TIMESTAMPDIFF(YEAR, P.`data_Nascimento`, NOW()) 
    FROM funcionario F, pessoa P
   INNER JOIN `pessoa` ON (F.`cpf_funcionario` = P.`cpf`);

The problem here is at the junction, should remove the section , pessoa P, then your view would look like this:

CREATE VIEW IdadeFuncionarios AS
SELECT TIMESTAMPDIFF(YEAR, P.`data_Nascimento`, NOW()) 
  FROM funcionario F
 INNER JOIN `pessoa` ON (F.`cpf_funcionario` = P.`cpf`);

But this view would be just returning the age field without telling which person belongs.

I made an example based on your tables with a small adaptation on funcionarios removing the field idade in accordance with @Motta’s comment that stated that 'It makes no sense to record "age" in the database, record the date of birth and calculate it.', in this way the age shall be calculated in the view.
I added the field salario to illustrate the situation in which the data view of the tables should return: Name, Salary, Age.
Example is available at: SQL Fiddle

For this solution, as an example, could use the following view:

CREATE VIEW FuncionarioSalarioIdade AS
   SELECT P.nome, F.salario, P.`data_Nascimento`, 
          Cast(NOW() as date) as Hoje, 
          TIMESTAMPDIFF(YEAR, P.`data_Nascimento`, NOW()) as idade
     FROM funcionario F 
    INNER JOIN `pessoa` P ON (F.`cpf_funcionario` = P.`cpf`);

Follow the full example code:

Mysql 5.6 Schema Setup:

create table pessoa
(
    cpf int not null
        primary key,
    nome varchar(100) not null,
    data_Nascimento date not null,
    cep varchar(9) null,
    telefone varchar(100) null
);
insert into pessoa (cpf, nome, data_Nascimento, cep, telefone) values (123, 'Maria', '1981-01-01', null, null);
insert into pessoa (cpf, nome, data_Nascimento, cep, telefone) values (456, 'Paulo', '1981-11-01', null, null);

create table funcionario
(
    cpf_funcionario int not null
        primary key,
    salario decimal(10,2) null,
    constraint funcionario_ibfk_1
        foreign key (cpf_funcionario) references pessoa (cpf)
            on update cascade
);
insert into funcionario (cpf_funcionario, salario) values (123, 15123.52);
insert into funcionario (cpf_funcionario, salario) values (456, 10456.74);

CREATE VIEW FuncionarioSalarioIdade AS
SELECT P.nome, F.salario, P.`data_Nascimento`, Cast(NOW() as date) as Hoje, TIMESTAMPDIFF(YEAR, P.`data_Nascimento`, NOW()) as idade
FROM funcionario F
INNER JOIN `pessoa` P
ON (F.`cpf_funcionario` = P.`cpf`);

Consultation 1:

Select * from FuncionarioSalarioIdade

Upshot:

name	salary	data_Nascimento	Today	age
Maria	15123.52	1981-01-01	2021-07-16	40
Paul	10456.74	1981-11-01	2021-07-16	39

I put the year of the date of birth of both people as born in 1981 to show that the month of birth and the current date, today, are being considered to carry out the calculation.