DELIMITER $$
CREATE TRIGGER Up_Credit AFTER INSERT ON sc
FOR EACH ROW
BEGIN
IF (NEW.Grade>60)
IF (SELECT sno FROM credits WHERE credits.sno=NEW.sno) IS NULL
INSERT INTO credits VALUES (NEW.sno,0,1)
ELSE
UPDATE credits SET Nopass = Nopass+1 WHERE ( credits.sno=NEW.sno )
ELSE
IF (SELECT sno FROM credits WHERE credits.sno=NEW.SNO) IS NULL
INSERT INTO credits VALUES (NEW.sno,(SELECT Credit FROM courses WHERE courses.cno=NEW.cno),0)
ELSE
UPDATE credits SET SumCredit= sumCredit+(SELECT Cerdit FROM courses WHERE courses.cno=NEW.cno)
END IF
END $$各位大神 我上面那段代码哪里错了啊 调试了一个晚上 查了手册 都没发现哪里错了 跪求各位大神给点力支持下
下面是建表文件 那些表是没错误的 就是上面那段代码错了
CREATE TABLE `courses` (
`CNO` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '',
`CNAME` varchar(225) CHARACTER SET utf8 DEFAULT NULL,
`LHOUR` int(11) DEFAULT NULL,
`CREDIT` int(11) DEFAULT NULL,
`SEMESTER` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`CNO`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `credits` (
`sno` varchar(10) NOT NULL,
`SumCredit` int(11) DEFAULT NULL,
`NoPass` int(11) DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `sc` (
`SNO` varchar(10) NOT NULL DEFAULT '',
`CNO` varchar(10) NOT NULL DEFAULT '',
`GRADE` double NOT NULL,
PRIMARY KEY (`SNO`,`CNO`),
KEY `CNO` (`CNO`),
CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `students` (`SNO`),
CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`CNO`) REFERENCES `courses` (`CNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `students` (
`SNO` varchar(10) NOT NULL DEFAULT '',
`SNAME` varchar(50) DEFAULT NULL,
`SEX` varchar(1) DEFAULT NULL,
`BDATE` date DEFAULT NULL,
`HEIGHT` double DEFAULT NULL,
`DEPARTMENT` varchar(50) DEFAULT NULL,
PRIMARY KEY (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TRIGGER Up_Credit AFTER INSERT ON sc
FOR EACH ROW
BEGIN
IF (NEW.Grade>60)
IF (SELECT sno FROM credits WHERE credits.sno=NEW.sno) IS NULL
INSERT INTO credits VALUES (NEW.sno,0,1)
ELSE
UPDATE credits SET Nopass = Nopass+1 WHERE ( credits.sno=NEW.sno )
ELSE
IF (SELECT sno FROM credits WHERE credits.sno=NEW.SNO) IS NULL
INSERT INTO credits VALUES (NEW.sno,(SELECT Credit FROM courses WHERE courses.cno=NEW.cno),0)
ELSE
UPDATE credits SET SumCredit= sumCredit+(SELECT Cerdit FROM courses WHERE courses.cno=NEW.cno)
END IF
END $$各位大神 我上面那段代码哪里错了啊 调试了一个晚上 查了手册 都没发现哪里错了 跪求各位大神给点力支持下
下面是建表文件 那些表是没错误的 就是上面那段代码错了
CREATE TABLE `courses` (
`CNO` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '',
`CNAME` varchar(225) CHARACTER SET utf8 DEFAULT NULL,
`LHOUR` int(11) DEFAULT NULL,
`CREDIT` int(11) DEFAULT NULL,
`SEMESTER` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`CNO`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `credits` (
`sno` varchar(10) NOT NULL,
`SumCredit` int(11) DEFAULT NULL,
`NoPass` int(11) DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `sc` (
`SNO` varchar(10) NOT NULL DEFAULT '',
`CNO` varchar(10) NOT NULL DEFAULT '',
`GRADE` double NOT NULL,
PRIMARY KEY (`SNO`,`CNO`),
KEY `CNO` (`CNO`),
CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `students` (`SNO`),
CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`CNO`) REFERENCES `courses` (`CNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `students` (
`SNO` varchar(10) NOT NULL DEFAULT '',
`SNAME` varchar(50) DEFAULT NULL,
`SEX` varchar(1) DEFAULT NULL,
`BDATE` date DEFAULT NULL,
`HEIGHT` double DEFAULT NULL,
`DEPARTMENT` varchar(50) DEFAULT NULL,
PRIMARY KEY (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TRIGGER Up_Credit AFTER INSERT ON sc
FOR EACH ROW
BEGIN
declare bExist int default 0;
IF (NEW.Grade>60) then
SELECT count(*) into bExist FROM credits WHERE credits.sno=NEW.sno;
IF bExist>0 THEN
INSERT INTO credits VALUES (NEW.sno,0,1);
ELSE
UPDATE credits SET Nopass = Nopass+1 WHERE ( credits.sno=NEW.sno );
end if;
ELSE
SELECT count(*) into bExist FROM credits WHERE credits.sno=NEW.sno;
IF bExist>0 THEN
INSERT INTO credits VALUES (NEW.sno,(SELECT Credit FROM courses WHERE courses.cno=NEW.cno),0);
ELSE
UPDATE credits SET SumCredit= sumCredit+(SELECT Cerdit FROM courses WHERE courses.cno=NEW.cno);
end if;
END IF;
END $$
CREATE TRIGGER Up_Credit AFTER INSERT ON sc
FOR EACH ROW
BEGIN
IF (NEW.Grade>60) THEN
SELECT sno INTO @sno FROM credits WHERE credits.sno=NEW.sno;
IF @sno IS NULL THEN
INSERT INTO credits VALUES (NEW.sno,0,1);
INSERT INTO credits VALUES (NEW.sno,(SELECT Credit FROM courses WHERE courses.cno=NEW.cno),0);
ELSE
UPDATE credits SET Nopass = Nopass+1 WHERE ( credits.sno=NEW.sno );
UPDATE credits SET SumCredit= sumCredit+(SELECT Cerdit FROM courses WHERE courses.cno=NEW.cno);
END IF;
END IF;
END $$
这个实验要求在一张视图里面加数据时,触发程序,完成一张表的更新。但是,我不知道怎么在视图插入数据时,触发程序,老是报错。还有,怎么在Mysql里面输出error 跪求各位大神帮忙 下面是代码错误的地方:
DELIMITER $$
CREATE TRIGGER Up_stuview BEFORE INSERT ON student_grade
FOR EACH ROW
BEGIN
DECLARE snumber INT DEFAULT 0;
DECLARE cnumber INT DEFAULT 0;
SELECT COUNT(*) INTO snumber FROM Students WHERE Students.sname=NEW.sname;
SELECT COUNT(*) INTO cnumber FROM Courses WHERE Courses.CNAME=NEW.Cname;
IF (snumber>0) AND (cnumber>0) THEN
INSERT INTO sc VALUES ( (SELECT sno FROM students WHERE students.sname=NEW.sname),(SELECT cno FROM courses WHERE courses.cname=NEW.cname),NEW.Grade);
ELSE
(注:这里要输出error,但是我不知道怎么做)
END IF;
END $$
错误如下:
错误码: 1347
'student.student_grade' is not BASE TABLE
跪求各位大神帮忙 不胜感激
这个实验要求在一张视图里面加数据时,触发程序,完成一张表的更新。但是,我不知道怎么在视图插入数据时,触发程序,老是报错。还有,怎么在Mysql里面输出error 跪求各位大神帮忙 下面是代码错误的地方:
DELIMITER $$
CREATE TRIGGER Up_stuview BEFORE INSERT ON student_grade
FOR EACH ROW
BEGIN
DECLARE snumber INT DEFAULT 0;
DECLARE cnumber INT DEFAULT 0;
SELECT COUNT(*) INTO snumber FROM Students WHERE Students.sname=NEW.sname;
SELECT COUNT(*) INTO cnumber FROM Courses WHERE Courses.CNAME=NEW.Cname;
IF (snumber>0) AND (cnumber>0) THEN
INSERT INTO sc VALUES ( (SELECT sno FROM students WHERE students.sname=NEW.sname),(SELECT cno FROM courses WHERE courses.cname=NEW.cname),NEW.Grade);
ELSE
(注:这里要输出error,但是我不知道怎么做)
END IF;
END $$
错误如下:
错误码: 1347
'student.student_grade' is not BASE TABLE
跪求各位大神帮忙 不胜感激
这个实验要求在一张视图里面加数据时,触发程序,完成一张表的更新。但是,我不知道怎么在视图插入数据时,触发程序,老是报错。还有,怎么在Mysql里面输出error 跪求各位大神帮忙 下面是代码错误的地方:
DELIMITER $$
CREATE TRIGGER Up_stuview BEFORE INSERT ON student_grade
FOR EACH ROW
BEGIN
DECLARE snumber INT DEFAULT 0;
DECLARE cnumber INT DEFAULT 0;
SELECT COUNT(*) INTO snumber FROM Students WHERE Students.sname=NEW.sname;
SELECT COUNT(*) INTO cnumber FROM Courses WHERE Courses.CNAME=NEW.Cname;
IF (snumber>0) AND (cnumber>0) THEN
INSERT INTO sc VALUES ( (SELECT sno FROM students WHERE students.sname=NEW.sname),(SELECT cno FROM courses WHERE courses.cname=NEW.cname),NEW.Grade);
ELSE
(注:这里要输出error,但是我不知道怎么做)
END IF;
END $$
错误如下:
错误码: 1347
'student.student_grade' is not BASE TABLE
跪求各位大神帮忙 不胜感激