Am I doing something wrong? I have a table of student data named students of marks of all subjects:

My Table Looks Like This

Mysql Code that is giving the error is this:

DELIMITER //
CREATE TRIGGER after_data_insert 
AFTER INSERT ON students FOR EACH ROW 
BEGIN
    ALTER TABLE students  
    ADD COLUMN total DOUBLE(40,3) AFTER comp_marks ;
    UPDATE students SET total = math_marks phy_marks chem_marks eng_marks comp_marks ;
    ALTER TABLE students  
    ADD COLUMN CGPA DOUBLE(40,3) AFTER total ;
    UPDATE students SET CGPA = ((total/5)/80)*10 ;
END //
DELIMITER ;

Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger.

Why am I getting this error? If you can please share the solution.

CodePudding user response：

One of the basic concepts of a relational database is that all rows of a table have the same columns. If you alter the table to add a column once, it changes all the rows, and any subsequent rows automatically have the new column. You don't need to add the column every time you insert a new row. Just alter the table once, and not in a trigger.

It looks like you want to calculate a total and cgpa for the respective student as you insert it. You could do it with a trigger this way:

DELIMITER //
CREATE TRIGGER after_data_insert 
BEFORE INSERT ON students FOR EACH ROW 
BEGIN
    SET NEW.total = NEW.math_marks NEW.phy_marks NEW.chem_marks NEW.eng_marks NEW.comp_marks ;
    SET NEW.CGPA = ((NEW.total/5)/80)*10 ;
END //
DELIMITER ;

You must use BEFORE INSERT, not AFTER INSERT. Changing values in the row being inserted is possible only in a before trigger.

Use NEW.<column> to reference the columns of the new row you are inserting. The applies both to setting the column and referencing other columns in the expression that calculates the total or cgpa.