如何通过MySQL触发器实现用户选课自动递增报名人数

Alright, let's build the trigger you need to automatically increment the enrollment count in the Subject_Annex table whenever a user registers for a course via the Register table. First, let's confirm your existing table structures for context:

1. Users Table #

Stores basic user information:

CREATE TABLE IF NOT EXISTS `Users` ( 
    `Id_User` smallint(5) unsigned NOT NULL AUTO_INCREMENT, 
    `Firstname` varchar(20) NOT NULL, 
    `Lastname` varchar(20) NOT NULL, 
    PRIMARY KEY (`Id_User`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1002 ;

2. Subject Table #

Stores course names:

CREATE TABLE IF NOT EXISTS `Subject` ( 
    `PrimaryKey_Subject` varchar(4) NOT NULL, 
    `Subject_Name` int(5) NOT NULL, 
    PRIMARY KEY (`PrimaryKey_Subject`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Quick heads up – Subject_Name is defined as int(5) here, which might be a typo if you're storing course names like "Math" or "Science". You might want to adjust that to varchar instead, but we'll proceed with your current structure.

3. Register Table #

Tracks user-course enrollments (many-to-many relationship):

CREATE TABLE IF NOT EXISTS `Register` ( 
    `ForeignKey_User` smallint(5) unsigned NOT NULL, 
    `ForeignKey_Lesson` varchar(4) NOT NULL, 
    PRIMARY KEY (`ForeignKey_User`,`ForeignKey_Lesson`), 
    KEY `ForeignKey_User_I` (`ForeignKey_User`), 
    KEY `ForeignKey_Lesson` (`ForeignKey_Lesson`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. Subject_Annex Table #

Stores enrollment counts per course:

CREATE TABLE IF NOT EXISTS `Subject_Annex` ( 
    `PrimaryKey_Subject` varchar(4) NOT NULL, 
    `Number_Registered` int(5) NOT NULL, 
    PRIMARY KEY (`PrimaryKey_Subject`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Trigger Implementation #

We'll create an AFTER INSERT trigger on the Register table. This trigger will fire every time a new enrollment record is added, and update the corresponding course's enrollment count in Subject_Annex – handling both existing courses (increment count) and new courses (insert initial count of 1).

DELIMITER //

CREATE TRIGGER trg_update_enrollment_count
AFTER INSERT ON Register
FOR EACH ROW
BEGIN
    -- Insert or update the enrollment count for the course
    INSERT INTO Subject_Annex (PrimaryKey_Subject, Number_Registered)
    VALUES (NEW.ForeignKey_Lesson, 1)
    ON DUPLICATE KEY UPDATE Number_Registered = Number_Registered + 1;
END //

DELIMITER ;

How This Works: #

• Trigger Timing: AFTER INSERT ensures the trigger runs only after the new enrollment record is successfully added to Register.
• FOR EACH ROW: The trigger executes once for every new row inserted into Register.
• INSERT ... ON DUPLICATE KEY UPDATE: This handles two scenarios:
  1. If the course already exists in Subject_Annex, it increments Number_Registered by 1.
  2. If the course is being enrolled in for the first time (no record in Subject_Annex), it inserts a new record with Number_Registered set to 1.

Testing the Trigger #

To verify it works, you can run an insert on the Register table:

-- Insert a new enrollment (replace with valid user and course IDs)
INSERT INTO Register (ForeignKey_User, ForeignKey_Lesson) VALUES (1002, 'SCI1');

Then check the Subject_Annex table:

SELECT * FROM Subject_Annex WHERE PrimaryKey_Subject = 'SCI1';

You should see the Number_Registered value incremented (or set to 1 if it's the first enrollment).

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