Creation of Trigger to help add a field according to an operation

Question

Good colleagues. I have the following problem, I need when a loan insertion action is triggered to the table emp_emprestimo automatically update to the table called genero_frequencia_emprestimos where we have only two tuples defined for the male gender and the female gender depending on the gender of the user who borrowed the tuple and add +1 in the field gef_numeroutentes. However, the user’s gender is only recorded in the user’s use_table. How can I perform this operation? I tried to use the code snippet below but it didn’t work, however mysql accepted the code but the loan insertion gave error:

create trigger update_frequencia_emp_generoMasc after insert on emp_emprestimo for each row update genero_frequencia_emprestimos set gef_numeroutentes=gef_numeroutentes + 1 where gef_id=1 and new.emp_utenteid.ute_genero="M";

Below I leave the code of the tables: User table:

CREATE TABLE `ute_utente` (
  `ute_id` int(11) NOT NULL AUTO_INCREMENT,
  `ute_nome` varchar(100) NOT NULL,
  `ute_dataNascimento` date NOT NULL,
  `ute_genero` enum('F','M') NOT NULL,
  `ute_naturalidade` varchar(50) NOT NULL,
  `ute_bi` varchar(13) NOT NULL,
  `ute_nomePai` varchar(100) DEFAULT NULL,
  `ute_nomeMae` varchar(100) DEFAULT NULL,
  `ute_localTrabalho` varchar(50) DEFAULT NULL,
  `ute_contacto` varchar(9) DEFAULT NULL,
  `ute_estado` enum('Não permitido','Permitido') NOT NULL,
  `ute_numeroEmps` int(11) DEFAULT '0',
  `ute_dataRegisto` date DEFAULT NULL,
  `ute_instituicaoId` int(11) DEFAULT NULL,
  `ute_enderecoId` int(11) DEFAULT NULL,
  PRIMARY KEY (`ute_id`),
  UNIQUE KEY `ute_bi_UNIQUE` (`ute_bi`),
  KEY `ute_instituicaoId` (`ute_instituicaoId`),
  KEY `ute_enderecoId` (`ute_enderecoId`),
  CONSTRAINT `ute_utente_ibfk_4` FOREIGN KEY (`ute_instituicaoId`) REFERENCES `ins_instituicaoensino` (`ins_id`),
  CONSTRAINT `ute_utente_ibfk_5` FOREIGN KEY (`ute_enderecoId`) REFERENCES `ute_endereco` (`ute_enderecoid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

Loan table:

CREATE TABLE `emp_emprestimo` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_utenteId` int(11) DEFAULT NULL,
  `emp_livroId` int(11) DEFAULT NULL,
  `emp_dataRegisto` datetime DEFAULT NULL,
  PRIMARY KEY (`emp_id`),
  KEY `emp_utenteId` (`emp_utenteId`),
  KEY `emp_livroId` (`emp_livroId`),
  CONSTRAINT `emp_emprestimo_ibfk_1` FOREIGN KEY (`emp_utenteId`) REFERENCES `ute_utente` (`ute_id`),
  CONSTRAINT `emp_emprestimo_ibfk_2` FOREIGN KEY (`emp_livroId`) REFERENCES `liv_livro` (`liv_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

table where the operation should take place

CREATE TABLE `genero_frequencia_emprestimos` (
  `gef_id` int(11) NOT NULL AUTO_INCREMENT,
  `gef_genero` varchar(20) DEFAULT NULL,
  `gef_numeroUtentes` int(11) DEFAULT '0',
  `gef_numeroVezes` int(11) DEFAULT '0',
  PRIMARY KEY (`gef_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Answer 1

Ayrton, you can make one select into to get the gender in the table ute_utente and then compare on IF whether it is male (M) or female (F).

CREATE TRIGGER update_frequencia_emp_generoMasc after insert on emp_emprestimo
FOR EACH ROW BEGIN
   DECLARE genero_ varchar(2); 
   select genero into genero_ from ute_utente where ute_id = new.emp_utenteId; 
   IF genero_ = 'M' THEN 
        update genero_frequencia_emprestimos set gef_numeroutentes=gef_numeroutentes + 1 where gef_id=1; 
   ELSEIF genero_ = 'F' THEN 
        update genero_frequencia_emprestimos set gef_numeroutentes=gef_numeroutentes + 1 where gef_id=2; 
   END IF; 
END;