Set age from date of birth in another table

Asked

Viewed 51 times

0

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?

mysql sql database

  • It makes no sense to record "age" in the database, record the date of birth and calculate it. If it is the age on a given date and only treat (instead of 'now') one can still use a calculated column for this https://devtools.com.br/blog/how-calculator-a-idade-no-mysql/ http://www.bosontreinamentos.com.br/mysql/criando-e-usando-colunas-generatedfields-calculated-in-tables-no-mysql/

  • I agree that it does not make sense, but the application needs to record "age". I’m having difficulty finding material that helps me in this sense. If I was on the same table with this Rigger I could solve

  • Could make a view with the calculated field. Create a view. Resolve?

  • And what would that view look like? Can I see from doing a workaround rs

  • here is another question that shows how to calculate: https://answall.com/questions/10403/howto calculatr-a-idade-basendo-na-birth date-no-mysql-based no-m%C3%aas-e-d, so a view as @Clarckmaciel suggested

1 answer

2


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.

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.