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