1
I have 3 tables:
Usuarios (id, nome)
Desafios (id, descricao)
Usuarios_tem_desafios (id_usuario, id_desafio, status)
The goal is, when adding a new user, to fill the table Usuarios_tem_desafios with the link between the added user and the registered challenges.
1
You can implement a Trigger AFTER INSERT in Usuarios. The idea is to use the command INSERT ... SELECT to select all challenges and insert corresponding entries in Usuarios_tem_desafios:
DELIMITER //
CREATE TRIGGER novos_desafios AFTER INSERT ON Usuarios
FOR EACH ROW
BEGIN
INSERT INTO Usuarios_tem_desafios
(id_usuario, id_desafio, status)
SELECT NEW.id, id, 'novo'
FROM Desafios;
END;//
DELIMITER ;
See working on Paiza.
That said, while the OP made it clear in the comments that this is an academic project, I advise avoiding to the maximum deal with business rules in triggers. This little example is definitely dealing with business rules:
The problem is that in real Software business rules can evolve over time, because of this, it is always a good idea to keep code centralized on an easy maintenance layer. Triggers are the opposite of that, the code ends up spread and maintenance can quickly become a headache.
There’s nothing wrong with wearing triggers to deal with Cross-Cutting Concerns, for example, to keep audit records, etc. But my recommendation is always to try to minimize the use of triggers.
All right, man. Thanks for the tip!
Hi Marcelo, no problem. If your problem has been solved please do not forget to accept the answer in question (as well as vote positively on useful answers).
0
given your request rewrote, due to the changes made prefer to post as another response. Like the TRIGGER does not accept parameter, to add a direct user in the match with a challenge already proposed, we would have to have the challenge id, due to this deficiency it was necessary to create a field in the user table called DesafioAtual of the kind INT that by default receives 1 which refers to the first challenge of the Challenge Table. With this the user will have by default his first challenge as soon as it is included.
After this inclusion TRIGGER goes into action Running an INSERT in Starts;
As I thought, once the user completes a certain challenge and leaves for the next one, you use an UPDATE to change the Current Challenge field and receive the new challenge number.
Now with the first TRIGGER model, use as an example to create a new AFTER UPDATE. Inside it repeats the query INSERT INTO Partidas(UsuarioID, DesafioID) VALUES(NEW.ID, NEW.DesafioAtual); and ready!
Follow Imgem below:
As was not expected and given the last comment I used While to insert the 15 records to each user inserted.
I hope I may have solved.
Dude, tell me if it’s possible to do this: within the trigger, create a variable that receives the amount of registered challenges. Loop over that amount and, within the loop, insert the user id and challenge id (which in case will be the loop counter). Got it?
yes loop is possible inside the TRIGGER, only the trigger will happen before or after an INSERT, DELETE and UPDATE. Have to get the table columns using NEW or OLD. Let me get this straight: with each user inserted, we can use the loop in TRIGGER to add all the challenges available to a user. That’s it?
Yes. I have 15 pre-registered challenges. When I add a new user, Trigger must enter 15 records in the Users_tem_challenges table, where each record is the user id entered and the id of each of the 15 challenges.
@Marceloaugusto, see if this issue is possible to solve the problem now.
Browser other questions tagged mysql trigger
You are not signed in. Login or sign up in order to post.
In your case, I think I’d better use Procedures or
Functions– Valdeir Psr
@Anthonyaccioly you think this is a complex rule for a Rigger?
– Marcelo Augusto
Not initially, but the point is that this seems to me to be related to business and not infrastructure (I am old-fashioned in this sense, use triggers for orthogonal needs such as logs, date updates, etc.). Today the rule is to insert all challenges for all new users, tomorrow the rule may have a complicated filter according to the user’s registration characteristics, then the Feature can evolve to use multiple tables, etc. I believe the best policy is to try to keep business rules in a single layer.
– Anthony Accioly
@Anthonyaccioly for this case no. I understood your advice and would even accept it if I knew it could become more complex. But this is just to save you the trouble of not inserting one by one, as I will not create a page for the user to register. I myself will register directly at the bank, because it is only for academic purposes.
– Marcelo Augusto