MySQL Auto Insert value to a column based on a value from a different table’s column with using Trigger

I am very new to mysql, and I want to ask for help about creating Trigger.

I have two tables UserTable and ProductTable
Table 1 UserTable, with two column:
UserID Rating

Table2 Product Table, with three column:
Product ID, UserID, Rating.

Whenever a new row is created in ProductTable, a new product ID is given, and userID will be updated based on which user created the row.

For Product Table’s Rating, I want it to refer to the value in Rating in UserTable where UserID in both tables are the same.

But I am not sure how to do it, will anyone kindly help me?
I think using Trigger is the way to do, please correct me if I am incorrect.

CREATE TRIGGER Add Rating
AFTER INSERT ON ProductTable
BEGIN
    INSERT INTO ProductTable(rating) 
    VALUES (UserTable.rating)
    Where ProductTable.UserID = UserTable.UserID;
End

I would caution against using triggers where you should be using other methods. Triggers are nice for temporary fixes when you can’t fix the application or database structure right away, but if you use them as an permanent and integral part of the workflow, things become confusing.

You table structure doesn’t seem right. Instead I’d suggest:

product:

• product_id (primary key)
• other columns for the product

user:

• user_id (primary key)
• other columns for the user

product_rating:

• product_id (foreign key to product table)
• user_id (foreign key to user table)
• rating
• (product_id, user_id) is a composite primary key

This table structure makes the trigger unnecessary, and it also allows more than one rating for each product. If you want to allow a certain user to rate a certain product more than once, then you need a special column to be the primary key as in the other tables, call it e.g. product_rating_id.