Search code examples
sqlmysqlmysql-workbench

mysql trigger is not working for stipulated query why

DROP DATABASE IF EXISTS expt13;
CREATE DATABASE expt13;
USE expt13;
CREATE TABLE student(tid INT NOT NULL PRIMARY KEY, name VARCHAR(30), sub_1 int2, sub_2 int2, sub_3 int2, total int3, percentage int3);
DESC student;
INSERT INTO student (tid,name,sub_1,sub_2,sub_3) values
(1,"priya",23,45,56),
(2,"shree",45,56,67),
(3,"mala",45,32,65);
DELIMITER //
CREATE TRIGGER stud_marks before insert ON student
for each row 
begin
update student set student.total=student.sub_1+student.sub_2+student.sub_3 where(student.total is null);
update student set student.percentage=student.total*60/100 where(student.percentage is null);
 end//
 DELIMITER ;
 SELECT * FROM student;

the trigger was created successfully but not working in the query

output

sql and I expect the output with triggers

Solution

  • Use generated column.

    CREATE TABLE student(
  tid INT NOT NULL PRIMARY KEY,
  name VARCHAR(30),
  sub_1 int2,
  sub_2 int2, 
  sub_3 int2,
  total int3 AS (sub_1+sub_2+sub_3),
  percentage int3 AS ((sub_1+sub_2+sub_3)*60/100));
INSERT INTO student (tid,name,sub_1,sub_2,sub_3) values
(1,"priya",23,45,56),
(2,"shree",45,56,67),
(3,"mala",45,32,65);
SELECT * FROM student;

    Records: 3  Duplicates: 0  Warnings: 0
    tid name sub_1 sub_2 sub_3 total percentage
    1 priya 23 45 56 124 74
    2 shree 45 56 67 168 101
    3 mala 45 32 65 142 85

    fiddle

    PS. percentage formula seems to be strange..