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

Asked

Viewed 22 times

0

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

inserir a descrição da imagem aqui

1 answer

0

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;
  • I tried to adapt the code to mine but it didn’t work, it was a mistake..

  • @Ayrtonpereira does not need to adapt the code, just understand the idea, in your Rigger you make a select in the table where you have the gender to see if it is male or female, and then update according to each gender.

Browser other questions tagged

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