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
I tried to adapt the code to mine but it didn’t work, it was a mistake..
– Ayrton Pereira
@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.
– Murilo Portugal