mysql，触发器内不知道怎么检查学生已经选修某节课

问题：建立一个INSTEAD OF触发器，每当删除课程表中记录时，先检查此课程是否已被选修，如选修则不允许删除，且给出提示信息“此课程已有学生已选修，无法删除”

建表代码：

DROP DATABASE IF EXISTS School;
CREATE DATABASE School charset utf8;
USE School;

DROP TABLE IF EXISTS s;
create table s(
    SNO varchar(45),
    SNAME varchar(45) ,
    SEX varchar(20) check ( SEX in ('男','女')),
    AGE int,
    DEPT varchar(20),
    primary key (sno)
);

DROP TABLE IF EXISTS c;
CREATE TABLE c (
                CNO INT,
                CNAME varchar(45),
                CCREDIT int,
                teacher varchar(45),
                cpno int,
                PRIMARY KEY (CNO),
                FOREIGN KEY (cpno) REFERENCES c(cno)
                ON DELETE SET NULL
                ON UPDATE CASCADE
);


DROP TABLE IF EXISTS sc;
CREATE TABLE sc (
                SNO varchar(20) NOT NULL,
                CNO INT NOT NULL,
                GRADE INT DEFAULT NULL,
                PRIMARY KEY (SNO,CNO),
                FOREIGN KEY (SNO) REFERENCES s(SNO),
                FOREIGN KEY (CNO) REFERENCES c(CNO)
                ON DELETE CASCADE
                ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `g-log`;
CREATE TABLE `g-log` (
    SNO varchar(20),
    GRADE SMALLINT
);

DROP TRIGGER IF EXISTS DML;
CREATE TRIGGER DML AFTER UPDATE ON SC
FOR EACH ROW
BEGIN
    IF(NEW.GRADE != OLD.GRADE ) THEN
        INSERT INTO `g-log` VALUES (NEW.SNO,NEW.GRADE);
    END IF;
END;

#这个触发器不会弄
 #DROP TRIGGER IF EXISTS INSTEAD_OF;
#CREATE TRIGGER INSTEAD_OF BEFORE DELETE ON C
#FOR EACH ROW
#BEGIN
# select cno from c where cno in (select cno from c natural join sc);
#     IF EXISTS(CNO) THEN
# #         SELECT '此课程已有学生选修，无法删除';
# #         ROLLBACK;
#     END IF;
#END;

INSERT INTO s values('200115121','LIYONG','男',20,'管理系');
INSERT INTO s values('200011411','LIHUA','男',20,'金融系');
INSERT INTO s values('200265327','LIPING','女',19,'计算机');
INSERT INTO s values('200213808','HUJING','女',22,'计算机');
INSERT INTO s values('199919841','WANGYANG','男',21,'机械系');
INSERT INTO s values('200108357','YUHUA','男',20,'计算机');

INSERT INTO c values(1,'DATABASE',2,'HU',1);
INSERT INTO c values(2,'OS',2,'WANG',2);
INSERT INTO c values(3,'INTERNET',2,'TIAN',3);
INSERT INTO c values(4,'COMPSYSTEM',2,'BAI',4);


INSERT INTO sc values('200115121', 1, 67);
INSERT INTO sc values('200115121', 3, -1);
INSERT INTO sc values('200213808', 1, -1);
INSERT INTO sc values('200213808', 2, 97);
INSERT INTO sc values('200213808', 3, 57);
INSERT INTO sc values('200011411', 1, 86);
INSERT INTO sc values('199919841', 1, 73);
INSERT INTO sc values('200265327', 1, 70);
INSERT INTO sc values('200265327', 2, 77);
INSERT INTO sc values('200108357', 1, 69);
INSERT INTO sc values('200108357', 3, 89);

求大佬指点

problem solved,去Stackoverflow查到一个叫SQL STATE 45000的语句，该语句会爆出一个错误并且允许用户设定报错信息，原文是：

To signal a generic SQLSTATE value, use '45000', which means “unhandled user-defined exception.”

代码：

DROP TRIGGER IF EXISTS INSTEAD_OF;
CREATE TRIGGER INSTEAD_OF BEFORE DELETE ON C
FOR EACH ROW
BEGIN
    IF old.cno IN (select cno from c natural join sc) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '该课程已被选修，无法删除';
    END IF;
END;