Mysql - Derivative Value Triggers

Question

I looked it up and couldn’t find it anywhere.

I am starting in Database studies and need the following help: A table Gang has the following structure:

Id_class(auto incrementable) Max_students(maximum number of students) soma_alunos(column derived from the so much of students who studies in stapling)

This table is linked in a relationship with Study_em, another table that is also lingada with the table Student, thus making a link Many-To-Many. The table Study_em has only Foreign Keys, Id_student and Id_class.

My intention is, every time I insert Study_em the Id_student and Id_class, have the sum value added by 1 ensuring that it is less or equal max_student

In short: Every time I insert a tuple into the table Study_em sum +1 in derived attribute student.

Follow an example of the code that is not certain:

CREATE TRIGGER adcionaaluno AFTER INSERT ON Estuda_em

FOR EACH ROW begin

UPDATE turma.soma_alunos =  soma_alunos + 1

end

Answer 1

Hello, Pedro.

Your answer is close, but some details are still missing. Trigger really is AFTER INSERT and in the table Estuda_em however your code needs two more things:

1. Locate the exact item to be modified in the table turma
2. Correct the UPDATE to use the tag SET

The following code - which expands from yours - achieves this:

CREATE TRIGGER adcionaaluno AFTER INSERT ON Estuda_em

FOR EACH ROW begin

UPDATE turma SET turma.soma_alunos =  soma_alunos + 1 WHERE turma.Id_turma = NEW.ID_turma;

END