Mysql - Derivative Value Triggers

Asked

Viewed 713 times

0

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

1 answer

0


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

Browser other questions tagged

You are not signed in. Login or sign up in order to post.