Mysql - Triggers of derived values

Navigation
0

I researched and found nowhere.

I'm starting to study in Database and need the following help: A Class table has the following structure:

Id_turma (self-incrementing) Max_children (maximum number of students) student_sum (column derived from both students studying at the school)

This table is linked in a relationship with Student_in , another table that is also paired with the Student table, thereby making a Many-To-Many connection. The Study_in table has only foreign keys, child_id and cell_id.

My intention is that every time I enter Student_in the child_id and the_id, have the child_sum value added by 1 ensuring that it is less than or equal to max_aluno

In short, every time I insert a tuple into the Study_in table, add +1 in the derived child_sum attribute.

Here is an example of the code that is not right: 

CREATE TRIGGER adcionaaluno AFTER INSERT ON Estuda_em

FOR EACH ROW begin

UPDATE turma.soma_alunos =  soma_alunos + 1

end
    
asked by anonymous 03.06.2015 / 05:22

1 answer

0

Hello, Peter.

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

  • Find the exact item to be modified in the turma table
  • Correct UPDATE to use tag SET

    • The following code - which I expanded from your - gets 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
        
    03.06.2015 / 05:38
    Change value of all ssearch in a request from Jquery DataTable Fatal error in login system