sql 触发器实现外键约束

Question

触发器 实现外键约束
数据插入失败

在插入课程记录前学生表中找到是否存在此学号和课程表中是否实现此课程

BEGIN 
  DECLARE xn INT;
  declare xn1 int;
  DECLARE msg VARCHAR(1000);
  SELECT count(s.sno) INTO xn FROM stu s WHERE s.sno=NEW.sno;
  SELECT count(s.cno) INTO xn1 FROM cou s WHERE s.cno=NEW.cno;
  IF xn != 1 and xn1!=-1 THEN  
  set msg = "Cannot add or update a row: a foreign key constraint on scores(name) fails.";
  SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = msg; #调用中断实现拒绝插入
  END IF;
end

sc表定义

CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(4,1) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

stu表

CREATE TABLE `stu` (
  `sno` char(4) NOT NULL,
  `sname` char(8) NOT NULL,
  `sex` tinyint DEFAULT '1',
  `mno` char(2) DEFAULT NULL,
  `birdate` datetime DEFAULT NULL,
  `memo` text,
  `photo` varchar(30) DEFAULT NULL,
  `E_MAIL` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sno`),
  UNIQUE KEY `sno` (`sno`),
  KEY `Stu_Major` (`mno`),
  CONSTRAINT `stu_chk_1` CHECK (((`sex` = 0) or (`sex` = 1))),
  CONSTRAINT `stu_chk_2` CHECK ((`sno` like _utf8mb4'S%')),
  CONSTRAINT `stu_chk_3` CHECK ((`E_MAIL` like _utf8mb4'____@____.___'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

cou表定义

CREATE TABLE `cou` (
  `cno` char(4) NOT NULL,
  `cname` varchar(30) DEFAULT NULL,
  `credit` smallint DEFAULT NULL,
  `ptime` char(5) DEFAULT NULL,
  `teacher` char(10) DEFAULT NULL,
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

添加对sc表的sno,cno约束后数据插入不了了，是什么问题 怎么解决呀？？

Answer 1

怎么光有begin和end，作为一个触发器，总要有before或者after之类的触发条件吧
目测是NEW.cno出错了，问题是sql不全，看不出来你这里new到底代表的是哪个表