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:
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_id (primary key)
- other columns for the product
- user_id (primary key)
- other columns for the user
- product_id (foreign key to product table)
- user_id (foreign key to user table)
- (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.