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:
- When entering a user it should be associated with new challenges.
- A challenge associated with a user should start at "new" status (kick my)
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.
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