Automatic update of select-table sql (trigger)

I am developing a small database of a a school, using MySQL. I have a table for professors, with id, name and email, and a table for lectures,with id, name of lecture, and the edition of that class. Then, I have an intermediary table for both professors and lectures.

As you can see bellow my last table includes a column for evaluation. Where, for each lecture a professor receives a score.

  CREATE TABLE professor ( 
professor_id INT PRIMARY KEY,
professor_name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL UNIQUE);

CREATE TABLE lecture (
lecture_id INT PRIMARY KEY,
lecture_name VARCHAR(50) NOT NULL,
edition INT NOT NULL);

CREATE TABLE lecture_prof (
professor_id INT,
lecture_id INT,
Evaluation INT,
FOREIGN KEY (professor_id) REFERENCES professor (professor_id),
FOREIGN KEY (lecture_id) REFERENCES lecture (lecture_id));

Now, I want the average evaluations of all the classes that the professors lectures. And I created a table with select.

   CREATE TABLE Avg_Evaluation AS SELECT 
p.professor_id,p.professor_name, AVG(Evaluation) AS Avg_Eval
    FROM professor p, lecture_professor lp
    WHERE p.professor_id=lp.professor_id
    GROUP BY professor_id, professor_name;

The table works but it has a problem, the only values it show are values that are already in the parents table (lecture_professor) before the table Avg_evaluation is created. And If I want to add new professors and new editions the table does not update automatically.

How can I solve this? I also thought I could do an updatedable view but I cannot use the arithmetic functions like AVG.

I wanted to do a trigger, for after inserting any new values on the lecture_professor table. but I don't know how.

Thank you for your help!

EDIT: here are some dummy values. and corrections.

    INSERT INTO faculty 
VALUES
(1,'Ana ','as@email.com'), 
(2,'Peter','pt@email.com'),
(3,'Mitchel','mm@email.org');

INSERT INTO lecture VALUES (1, 'Econ', 20),(2, 'History', 1),(3, 'Social Studies', 2);

INSERT INTO lecture_professor VALUES (1,1,3),(2,1,2),(2,2,4),(2,3,4),(1,3,2),(3,1,3);

Solution

Here's your data with a view

drop table if exists lecture_professor;
drop table if exists lecture,professor;

CREATE TABLE professor ( 
professor_id INT PRIMARY KEY,
professor_name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL UNIQUE);

CREATE TABLE lecture (
lecture_id INT PRIMARY KEY,
lecture_name VARCHAR(50) NOT NULL,
edition INT NOT NULL);

CREATE TABLE lecture_professor (
professor_id INT,
lecture_id INT,
Evaluation INT ,
FOREIGN KEY fk1 (professor_id) REFERENCES professor (professor_id),
FOREIGN KEY fk2 (lecture_id) REFERENCES lecture (lecture_id)
);

insert into professor values
(1,'Ana ','as@email.com'), 
(2,'Peter','pt@email.com'),
(3,'Mitchel','mm@email.org');

INSERT INTO lecture VALUES (1, 'Econ', 20),(2, 'History', 1),(3, 'Social Studies', 2);

INSERT INTO lecture_professor VALUES (1,1,3),(2,1,2),(2,2,4),(2,3,4),(1,3,2),(3,1,3);

drop view if exists v;
create view v as 
SELECT p.professor_id,p.professor_name, 
        coalesce(AVG(Evaluation),0) AS Avg_Eval
FROM professor p
left join lecture_professor lp on p.professor_id = lp.professor_id
GROUP BY professor_id,p.professor_name
; 

select * from v;

I don't know why you don't think a view cannot contain an aggregate function because the output looks fine to me

+--------------+----------------+----------+
| professor_id | professor_name | Avg_Eval |
+--------------+----------------+----------+
|            1 | Ana            |   2.5000 |
|            2 | Peter          |   3.3333 |
|            3 | Mitchel        |   3.0000 |
+--------------+----------------+----------+
3 rows in set (0.00 sec)

Please note the explicit join.