使用MySQL触发器实现插入评分后更新视频上传表字段

Hey there! Let's work through how to implement this requirement. You need to automatically update the video_upload table every time a new rating is added to video_ratings, right? Triggers are exactly what we need here—they let us run custom logic right after an insert happens. Here's a step-by-step breakdown:

Triggers are perfect for this kind of post-insert automation. Below is an example tailored for MySQL (syntax is similar for other databases like PostgreSQL, with minor tweaks):

Step 1: Create the Trigger #

DELIMITER //
CREATE TRIGGER update_video_upload_after_rating_insert
AFTER INSERT ON video_ratings
FOR EACH ROW
BEGIN
    -- Declare variables to hold our rating counts
    DECLARE bad_count INT;
    DECLARE average_count INT;
    DECLARE good_count INT;

    -- Calculate counts for each rating type, filtered to the newly rated video
    -- Replace `video_id` with your actual linking column between the two tables
    SELECT 
        COUNT(CASE WHEN RATINGS = 0 THEN 1 END),
        COUNT(CASE WHEN RATINGS = 1 THEN 1 END),
        COUNT(CASE WHEN RATINGS = 2 THEN 1 END)
    INTO bad_count, average_count, good_count
    FROM video_ratings
    WHERE video_id = NEW.video_id;

    -- Update the matching row in video_upload with the new counts
    UPDATE video_upload
    SET 
        RATE_BAD = bad_count,
        RATE_AVERAGE = average_count,
        RATE_GOOD = good_count
    WHERE video_id = NEW.video_id;
END //
DELIMITER ;

Key Details to Understand #

• AFTER INSERT ON video_ratings: This tells the database to execute the trigger logic right after a new row is added to the ratings table.
• FOR EACH ROW: Ensures the trigger runs once for every inserted row—so it works for both single ratings and bulk inserts.
• NEW Keyword: This refers to the row that was just inserted. We use NEW.video_id to target the specific video in video_upload that needs updating.
• Conditional Counting: The COUNT(CASE ...) statements let us tally up each rating value in one query instead of running separate COUNT calls, which is more efficient.

Quick Adjustments for Your Setup #

• If you're using PostgreSQL, you'll need to create a trigger function first, then attach it to the table (the core logic stays the same, just the wrapper syntax changes).
• If you have more rating values (like 3 for "excellent"), just add another COUNT(CASE WHEN RATINGS = 3 THEN 1 END) and update the corresponding field in video_upload.
• Double-check that video_ratings.video_id is properly linked to video_upload.video_id (ideally with a foreign key) to avoid updating the wrong rows.

内容的提问来源于stack exchange，提问作者Eshan Gurusinghe