我有两张表,其中一张名为att
如下
CREATE TABLE att (
SID varchar(50) NOT NULL,
CID varchar(50) NOT NULL,
Date date NOT NULL,
H1 varchar(1) NOT NULL DEFAULT 0,
H2 varchar(1) NOT NULL DEFAULT 0,
H3 varchar(1) NOT NULL DEFAULT 0,
H4 varchar(1) NOT NULL DEFAULT 0,
H5 varchar(1) NOT NULL DEFAULT 0,
H6 varchar(1) NOT NULL DEFAULT 0,
H7 varchar(1) NOT NULL DEFAULT 0,
H8 varchar(1) NOT NULL DEFAULT 0,
H9 varchar(1) NOT NULL DEFAULT 0,
H10 varchar(1) NOT NULL DEFAULT 0,
INDEX (SID, CID)
);
另一张表是per
具有以下字段:
SID CID 每
如何为以下内容编写触发器:
如果 h1-h0 中的任何字段发生更新att
然后表
更新per
栏目中的per
具有以下值的表:
((1总数-0总数)/(1总数+0总数))/100
提前致谢
我开发了一个触发器,但它不起作用,它在第 11 行显示错误,你能说出问题是什么吗?
create TRIGGER `att_up` AFTER UPDATE ON `attentance`
FOR EACH ROW BEGIN
DECLARE Zeros INT;
DECLARE Ones INT;
DECLARE total INT;
DECLARE atted FLOAT;
SELECT SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8))
INTO Zeros FROM attentance
WHERE StudID=NEW.StudID;
SELECT SUM(h1+h2+h3+h4+h5+h6+h7+h8)
INTO Ones FROM attentance
WHERE StudID=NEW.StudID;
SELECT SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8))+ SUM(h1+h2+h3+h4+h5+h6+h7+h8)
INTO total FROM attentance
WHERE StudID=NEW.StudID;
set atted=((ZEROS-Ones)/total)/100;
INSERT into per(per) values (atted);
END$$