Trigger to link multiple records

Question

Trigger to link multiple records

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have 3 tables:

Usuarios (id, nome)
Desafios (id, descricao) 
Usuarios_tem_desafios (id_usuario, id_desafio, status)

The goal is to add a new user by filling in the Usuarios_tem_desafios table with the link between the added user and the registered challenges.

mysql trigger

asked by anonymous 16.01.2018 / 13:02

2 answers

Mockar non-static method of a class containing a call from another static method Angular 5 route problems

score 1 · Answer 1

You can implement a trigger AFTER INSERT in Usuarios . The idea is to use the command INSERT ... SELECT to select all the 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 at Paiza.io

That said, while the OP made it clear in the comments that this is an academic project, I advise avoiding the most of dealing with business rules in triggers . This small example is certainly dealing with business rules:

When inserting a user it must be associated with new challenges.

A challenge associated with a user should start with the "new" status (my kick)

The problem is that in real software business rules can evolve over time, because of this, it's always a good idea to keep the code centralized in an easy-to-maintain layer. Triggers are the opposite of that, the code spreads and maintenance can quickly become a headache.

There is nothing wrong with using triggers to handle Cross-cutting concerns , for example to maintain audit records, etc. But my recommendation is to always try to minimize the use of triggers .

score 0 · Answer 2

Given your request, I rewrote it, due to the changes I made, I'd like to post it as another response. As TRIGGER does not accept parameter, to add a direct user in the match with a challenge already proposed, we would have to have the id of the challenge, due to this deficiency it was necessary to create a field in the users table called DesafioAtual of type INT which by default receives 1 that refers to the first challenge in the Challenge Table. With this the user will by default have their first challenge as soon as it is included.

After this inclusion the TRIGGER takes action Execute an INSERT in Matches; In the way I thought, as soon as the user completes a certain challenge and leaves the next one, you use an UPDATE to change the Current Challenge field and receive the number of the new challenge. Now with the first TRIGGER template, use as an example to create a new one of type AFTER UPDATE . Inside it, repeat the query INSERT INTO Partidas(UsuarioID, DesafioID) VALUES(NEW.ID, NEW.DesafioAtual); and that's it!

Follow Imgem below:

AsitwasnotexpectedandgiventhelastcommentIusedtheWhiletoinsertthe15recordstoeachuserentered.

I hope you have solved it.