用mysql创建存储过程和触发器时出现有关问题

用mysql创建存储过程和触发器时出现问题

我用的WampServer2.1a-x3集成的mysql,MySQL 客户端版本: mysqlnd 5.0.7-dev,每次创建存储过程和触发器时出现上图的错误,都说我第一行错,但又没发现错在哪?求高手看看..

SQL code

USE Sams2;DELIMITER $$CREATE PROCEDURE CJ_Data(in_xh CHAR(6),in_kch CHAR(3),in_cj INT(4))BEGIN     DECLARE in_count INT(4);     DECLARE in_xf    TINYINT(1);     DECLARE in_cjb_cj INT(4);     SELECT XF INTO in_xf FROM KCB WHERE KCH=in_kch;     SELECT COUNT(*) INTO in_count FROM CJB WHERE XH=in_xh AND KCH=in_kch;     SELECT CJ INTO in_cjb_cj FROM CJB WHERE XH=in_xh AND KCH=in_kch;     IF in_count>0 THEN     BEGIN         DELETE FROM CJB WHERE XH=in_xh and KCH=in_kch;         IF in_cjb_cj>60 THEN               UPDATE XSB set ZXF=ZXF-in_xf WHERE XH=in_xh;         END IF;     END;     END IF;     IF in_cj!=-1 THEN     BEGIN         INSERT INTO CJB VALUES(in_xh,in_kch,in_cj);         IF in_cj>60 THEN               UPDATE XSB SET ZXF=ZXF+in_xf WHERE XH=in_xh;         END IF;     END;     END IF;END$$DELIMITER;

SQL code

USE Sams2;DELIMITER $$CREATE TRIGGER Check_XSB_CJB AFTER DELETE    ON XSB FOR EACH ROWBEGIN    DELETE FROM CJB WHERE XH=OLD.XH;END$$DELIMITER;

[解决办法]
DELIMITER $$
DROP PROCEDURE IF EXISTS CJ_Data$$
CREATE PROCEDURE CJ_Data(in_xh CHAR(6),in_kch CHAR(3),in_cj INT(4))
BEGIN
DECLARE in_count INT(4);
DECLARE in_xf TINYINT(1);
DECLARE in_cjb_cj INT(4);
SELECT XF INTO in_xf FROM KCB WHERE KCH=in_kch;
SELECT COUNT(*) INTO in_count FROM CJB WHERE XH=in_xh AND KCH=in_kch;
SELECT CJ INTO in_cjb_cj FROM CJB WHERE XH=in_xh AND KCH=in_kch;
IF in_count>0 THEN
BEGIN
DELETE FROM CJB WHERE XH=in_xh AND KCH=in_kch;
IF in_cjb_cj>60 THEN
UPDATE XSB SET ZXF=ZXF-in_xf WHERE XH=in_xh;
END IF;
END;
END IF;
IF in_cj!=-1 THEN
BEGIN
INSERT INTO CJB VALUES(in_xh,in_kch,in_cj);
IF in_cj>60 THEN
UPDATE XSB SET ZXF=ZXF+in_xf WHERE XH=in_xh;
END IF;
END;
END IF;
END$$
DELIMITER ;

USE Sams2;
DELIMITER $$
CREATE TRIGGER Check_XSB_CJB AFTER DELETE
ON XSB FOR EACH ROW
BEGIN
DELETE FROM CJB WHERE XH=OLD.XH;
END$$
DELIMITER ;
[解决办法]
注意 空格
DELIMITER ;
[解决办法]
XSB上有多个TRIGGER？检查一下
[解决办法]
SHOW TRIGGERS ;
drop TRIGGER TRIGGERname

查看更多 下一篇