创建一个触发器，当向study表插入记录时，对成绩进行合法性验证，当成绩不合法时，不能插入该记录。

Question

使用sqlyog创建了三个数据库并且进行了数据的插入如下（无错误）

CREATE DATABASE liu;
USE liu;
CREATE TABLE student
(Sno VARCHAR(50) NOT NULL PRIMARY KEY,
Sname VARCHAR(50),
Age INT,
Sex VARCHAR(50),
Place VARCHAR(50));
CREATE TABLE Course
(Cno VARCHAR(50) PRIMARY KEY,
Cname VARCHAR(50),
Credit INT
)
CREATE TABLE Study
(
 Sno VARCHAR(50) NOT NULL,
 Cno VARCHAR(50) NOT NULL,
 Grage INT ,
 PRIMARY KEY(Sno,Cno)
)
INSERT INTO student
VALUES('201215121','李勇',20,'男','广东'),
('201215122','刘晨',19,'女','湖北'),
('201215123','王敏',18,'女','江西'),
('201215125','张立',19,'男','湖北'),
('201215126','汪明',18,'男','湖北');
INSERT INTO Course
VALUES('C01','数据库',3),
('C02','C语言',4),
('C03','操作系统',4),
('C04','数据结构',4),
('C05','软件工程',2);
INSERT INTO Study
VALUES('201215121','C01',92),
('201215121','C05',85),
('201215122','C01',90),
('201215122','C03',80),
('201215123','C02',87),
('201215123','C04',79);

对（1）和（2）进行了创建（无错误），如何求解（3），以及对存储器创建完成后，且0error，如何验证其功能符合要求

DROP PROCEDURE IF EXISTS student_info;
DELIMITER $$
CREATE PROCEDURE student_info()
BEGIN 
SELECT a.Sno,Sname,Cname,Grage,Credit
FROM Student a INNER JOIN Study b ON a.Sno=b.Sno INNER JOIN Course t ON b.Cno=t.Cno;
SELECT AVG(Grage)AS average,SUM(Credit) AS sumcredit
FROM Study INNER JOIN Course ON Study.Cno=Course.Cno
GROUP BY Sno;
END $$
DELIMITER ;
CALL student_info;

DROP TRIGGER IF EXISTS delete_trig;
DELIMITER $$
CREATE TRIGGER delete_trig
AFTER DELETE
ON Course FOR EACH ROW
BEGIN 
DECLARE xh CHAR(9);
SET xh=old.Cno;
DELETE FROM Study
WHERE Cno=xh;
END $$
DELIMITER ;

Answer 1

DROP TRIGGER IF EXISTS insert_trig;
DELIMITER $$
CREATE TRIGGER insert_trig
before insert
ON study FOR EACH ROW
BEGIN 
if new.Grage <0 or new.Grage >100 then 
signal sqlstate 'HY000' set message_text = '得分需要大于等于0，小于等于100';
end if;
END $$
DELIMITER ;

验证就是插入分数不合格的，比如小于0或者大于100的分数，会抛出错误

INSERT INTO Study
VALUES('201215121','C01',150)