KTU DBMS Lab

10.sql

/*
AIM:
Given Student Report Database, in which student marks assessment is recorded. In such schema, create a trigger so that the total and average of specified marks is automatically inserted whenever a record is inserted.

Student
Sid	name	Subj1	Subj2	Subj3	Total	Avg
*/

DELIMITER //

CREATE TRIGGER CalculateTotalAndAverage
BEFORE INSERT ON Student
FOR EACH ROW
BEGIN
    -- Calculate the Total
    SET NEW.Total = NEW.Subj1 + NEW.Subj2 + NEW.Subj3;
    
    -- Calculate the Average
    SET NEW.Avg = (NEW.Subj1 + NEW.Subj2 + NEW.Subj3) / 3;
END;
//

DELIMITER ;