sql - MySQL Trigger won't update rows when data inserted

Question

I am currently doing an assignment for university for a school database. I am using MySQL. I have a student table that links to a loan table to determine which loan the student has based on their student type. I want to make a trigger that will update the loan_id based on the given student_code. I have this trigger so far:

DELIMITER |

CREATE TRIGGER student_loan_insert_update
    AFTER INSERT
    ON student
    FOR EACH ROW 
    BEGIN
        DECLARE student_code VARCHAR(10);
        SET student_code = (SELECT student_code FROM student LIMIT 1);
    
        IF student_code = "F" THEN
            UPDATE student SET student.loan_id = "LN1" WHERE student_code = NEW.student_code;
        ELSEIF student_code = "FI" THEN
            UPDATE student SET student.loan_id = "LN2" WHERE student_code = NEW.student_code;
        ELSEIF student_code = "D" THEN
            UPDATE student SET student.loan_id = "LN3" WHERE student_code = NEW.student_code;
        ELSEIF student_code = "DI" THEN
            UPDATE student SET student.loan_id = "LN4" WHERE student_code = NEW.student_code;
        END IF;
    END;
|
DELIMITER ;

It doesn't throw any errors, but when I insert the data, none of the student loan_ids change and just remain as LN1. (See the insert data below):

INSERT INTO `student` (`student_id`,`curriculum_id`,`loan_id`,`student_forename`,`student_surname`,`student_code`,`student_dob`,`student_address`,`student_city`,`student_postcode`,`student_email`,`student_tele`,`student_fee_paid_td`) VALUES 
    ("P1","CR1","LN1","Axel","Prince","F","2003-02-21","8 Nascetur St.","Erchie","D6 1XK","[email protected]","+44 5610 275457",7459),
    ("P2","CR5","LN1","Howard","Thomas","F","2004-10-09","7 Nunc. Avenue","Bogor","H4Q 9MZ","[email protected]","+44 1672 205594",7689),
    ("P3","CR1","LN1","Nathan","Elliott","F","2004-08-04","6 Eu St.","Bierges","US6 0TC","[email protected]","+44 1300 961375",4260),
    ("P4","CR6","LN1","Piper","Torres","F","2002-05-21","3 Felis. Rd.","Khuzdar","ZI0 7QI","[email protected]","+44 6152 201055",1209),
    ("P5","CR2","LN1","Ralph","Mayo","F","2004-03-27","5 Praesent Av.","Lakeland","SZ7 5RJ","[email protected]","+44 0910 033565",1178),
    ("P6","CR1","LN1","Cade","Soto","FI","2002-05-10","3 Turpis St.","Saint-Oyen","RL4 2LC","[email protected]","+44 5197 328908",6640),
    ("P7","CR3","LN1","Erin","Gordon","FI","2003-09-30","1 A Avenue","Brussel","V7U 4DF","[email protected]","+44 5774 181109",2285),
    ("P8","CR6","LN1","Kibo","Roy","FI","2003-03-12","7 At, Street","León","E4H 8YD","[email protected]","+44 8826 578883",6967),
    ("P9","CR6","LN1","Petra","Haney","FI","2002-07-14","6 Risus. Rd.","Silvassa","G87 8NW","[email protected]","+44 4238 118987",3913),
    ("P10","CR5","LN1","Sage","Cleveland","FI","2002-06-03","2 Sapien, Rd.","Birmingham","S14 8QF","[email protected]","+44 2191 821405",5159),
    ("P11","CR4","LN1","Dakota","Hancock","D","2003-10-15","6 Elit Avenue","Fratta","S2C 0IG","[email protected]","+44 7220 779001",5391),
    ("P12","CR6","LN1","Elaine","Chan","D","2004-11-15","9 Quis Street","Opprebais","J1H 8YT","[email protected]","+44 0327 426178",8665),
    ("P13","CR7","LN1","Steven","Woodard","D","2004-07-26","2 Volutpat Av.","Oamaru","MK9 4CY","[email protected]","+44 2598 340795",4082),
    ("P14","CR6","LN1","Mannix","Buckner","D","2003-09-30","4 Donec Rd.","Helensburgh","H9B 2UH","[email protected]","+44 4133 663174",6748),
    ("P15","CR6","LN1","Hadassah","Wise","D","2002-09-24","3 Quisque Ave","Haverfordwest","IL9X 4CJ","[email protected]","+44 0564 934110",7268),
    ("P16","CR1","LN1","Kyra","Joseph","DI","2003-12-09","5 Neque. St.","Greifswald","NM7Q 6PS","[email protected]","+44 2011 247324",1655),
    ("P17","CR3","LN1","Claire","Aguilar","DI","2002-01-27","3 Aliquam Av.","Schoonaarde","N8 3QT","[email protected]","+44 9416 497087",1430),
    ("P18","CR1","LN1","Geoffrey","Morrison","DI","2002-01-13","9 Lectus Rd.","Cardiff","ZM18 2KM","[email protected]","+44 7470 007536",4694),
    ("P19","CR2","LN1","Tallulah","Jarvis","DI","2002-07-13","7 Nec Rd.","Wha Ti","M1J 0YQ","[email protected]","+44 8973 483491",7065),
    ("P20","CR1","LN1","Malik","Davis","DI","2004-09-28","3 Amet Av.","Pont-Saint-Martin","JG43 8WH","[email protected]","+44 5505 920997",4232);

And for additional information here is the loan insert data:

INSERT INTO loan VALUES 
    ("LN1",  "Full-time home student", "6000.00"),
    ("LN2", "Full-time international student", "9000.00"),
    ("LN3", "Distance-learning home student", "3000.00"),
    ("LN4",  "Distance-learning international student", "6000.00");

I am unsure whether I am doing the IF statement in the trigger wrong, or messing up the syntax anywhere, but any help would be greatly appreciated.

Thanks in advance!

EDIT: Here is the student CREATE script

CREATE TABLE student (
    student_id VARCHAR(7) NOT NULL,
    curriculum_id VARCHAR(7) NOT NULL,
    loan_id VARCHAR(7) NOT NULL,
    student_forename VARCHAR(10) NOT NULL,
    student_surname VARCHAR(10) NOT NULL,
    student_code VARCHAR(10) NOT NULL,
    student_dob DATE NOT NULL,
    student_address VARCHAR(20) NOT NULL,
    student_city VARCHAR(20) NOT NULL,
    student_postcode VARCHAR(10) NOT NULL,
    student_email VARCHAR(30) NOT NULL,
    student_tele VARCHAR(20) NOT NULL,
    student_fee_paid_td DECIMAL(7,2) NOT NULL,
    PRIMARY KEY (student_id),
    CONSTRAINT student_curriculum_fk FOREIGN KEY (curriculum_id) REFERENCES curriculum(curriculum_id),
    CONSTRAINT student_loan_fk FOREIGN KEY (loan_id) REFERENCES loan(loan_id),
    CONSTRAINT limit_student_codes CHECK (student_code = "F" OR student_code = "FI" OR student_code = "D" OR student_code = "DI")
);

And the output of the table.

Answer 1

As a general rule, when you want to update the row being inserted, you want a before insert trigger.

In addition, you can do this using a single statement using case rather than using if. So, I would suggest logic like this:

CREATE TRIGGER student_loan_insert_update BEFORE INSERT ON student
FOR EACH ROW 
BEGIN
    SET NEW.load_id = (CASE WHEN NEW.load_id = 'F'  THEN 'LN1'
                            WHEN NEW.load_id = 'FI' THEN 'LN2'
                            WHEN NEW.load_id = 'D'  THEN 'LN3'
                            WHEN NEW.load_id = 'DI' THEN 'LN4'
                            ELSE NEW.load_id
                       END);
END;