此博客探讨了如何在MySQL中创建一个名为Update_Status的触发器，当status表中的状态字段更新为3时，自动为problem表中对应的问题增加完成次数，并检查同一用户是否还有其他状态为3的问题，如有则更新user表中的已解决项目数。
DELIMITER|DROPTRIGGERIFEXISTSUpdate_Status|CREATETRIGGERUpdate_StatusAFTERUPDATEONstatusFOREACHROWBEGINIF(new.state=3)BEGINUPDATEproblemSETproAC=proAC+1WHEREproId=new.pro...
DELIMITER |
DROP TRIGGER IF EXISTS Update_Status|
CREATE TRIGGER Update_Status
AFTER UPDATE ON status
FOR EACH ROW
BEGIN
IF (new.state = 3)
BEGIN
UPDATE problem
SET proAC = proAC + 1
WHERE proId = new.proId;
IF ((SELECT COUNT(*) FROM status WHERE handle = new.handle AND proId = new.proId AND state=3) > 1)
BEGIN
UPDATE user
SET proSolved = proSolved + 1
WHERE handle = new.handle;
END;
END;
END;
|
phpmyadmin中：
错误
SQL 查询:
CREATE TRIGGER Update_Status AFTER UPDATE ON STATUS FOR EACH ROW BEGIN IF( new.state =3 ) BEGIN UPDATE problem SET proAC = proAC +1 WHERE proId = new.proId;
IF( (
SELECT COUNT( * )
FROM STATUS WHERE handle = new.handle
AND proId = new.proId
AND state =3 ) >1
)
BEGIN
UPDATE user SET proSolved = proSolved +1 WHERE handle = new.handle;
END ;
END ;
END ;
|
MySQL 返回：
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'BEGIN
UPDATE problem
SET proAC = proAC + 1
WHERE proId = new.proId;
' at line 6
展开
349
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
