插入后和更新后的MySQL触发器 [英] MySQL trigger after insert and after update
问题描述
我有两个表,其中一个名为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 Per
如何为以下内容编写触发器:
如果att
表中h1-h0的任何字段中发生更新,则
使用以下值更新per
表中的per
列:
(((总计1s-总计0s)/((总计1s +总计0s))/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$$
在定义触发器之前,请确保更改定界符.还要确保在创建表和触发器时使用相同的表名和列名(在示例中,您使用的是att
和attendance
以及SID
和StudID
).>
就这样,设置分隔符后,在MySQL 5.1.55中对触发器定义进行测试时,触发器定义没有引起错误.
delimiter $$
CREATE TRIGGER `att_up`
AFTER UPDATE ON `attendance`
FOR EACH ROW
BEGIN
DECLARE Zeros INT;
DECLARE Ones INT;
DECLARE total INT;
DECLARE attend FLOAT;
SELECT SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8)),
SUM(h1+h2+h3+h4+h5+h6+h7+h8),
SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8)) + SUM(h1+h2+h3+h4+h5+h6+h7+h8)
INTO Zeros, Ones, Total FROM attendance
WHERE SID=NEW.SID;
SET attend=((Zeros-Ones)/total)/100;
INSERT INTO per (SID, CID, per) values (NEW.SID, NEW.CID, attend)
ON DUPLICATE KEY UPDATE per=attend;
END$$
delimiter ;
I have two tables with one named att
as follows
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)
);
The other table is per
with following fields:
SID CID Per
How do I write a trigger for the following:
If an update occurs in any of the fields from h1-h0 on att
table then
update the per
column in the per
table with the following values:
((total no of 1s - total no of 0s)/(total no of 1s + total no of 0s))/100
Thanks in advance
i Developed a Trigger,but it not working,its saying error in line 11,can you say what is the problem??
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$$
Make sure you change the delimiter before defining the trigger. Also make sure you're using the same table and column names when you create the table and the trigger (you're using att
and attendance
, and SID
and StudID
, in your examples).
As it is, the trigger definition caused no error when I tested it in MySQL 5.1.55 after setting the delimiter.
delimiter $$
CREATE TRIGGER `att_up`
AFTER UPDATE ON `attendance`
FOR EACH ROW
BEGIN
DECLARE Zeros INT;
DECLARE Ones INT;
DECLARE total INT;
DECLARE attend FLOAT;
SELECT SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8)),
SUM(h1+h2+h3+h4+h5+h6+h7+h8),
SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8)) + SUM(h1+h2+h3+h4+h5+h6+h7+h8)
INTO Zeros, Ones, Total FROM attendance
WHERE SID=NEW.SID;
SET attend=((Zeros-Ones)/total)/100;
INSERT INTO per (SID, CID, per) values (NEW.SID, NEW.CID, attend)
ON DUPLICATE KEY UPDATE per=attend;
END$$
delimiter ;
这篇关于插入后和更新后的MySQL触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!