56,681
社区成员
发帖
与我相关
我的任务
分享
Error Code : 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 '@flag1='False';
end if;
if length(@mstxt)!=15 or length(@mstxt)!' at line 19
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
DELIMITER $$
USE `test`$$
DROP TRIGGER /*!50032 IF EXISTS */ `ana_trigger`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `ana_trigger` AFTER INSERT ON `r_message`
FOR EACH ROW BEGIN
IF NOT EXISTS(SELECT * FROM employee WHERE phone=SUBSTR(new.phone,3,11) ) THEN
INSERT INTO er_message(r_mes) VALUES(CONCAT_WS(' ','您非本公司员工,无权完成此操作',NULL,new.txt));
UPDATE r_message SET errType=6;
ELSE
SET @eeid=1;
SET @eename='';
SET @mstxt=new.txt;
SET @flag1='';
SELECT eid INTO @eeid FROM employee WHERE phone=SUBSTR(new.phone,3,11) ;
SELECT ename INTO @eename FROM employee WHERE phone=SUBSTR(new.phone,3,11) ;
IF new.txt NOT REGEXP '^[0-9]*#[0-9]*#[0-9]*#' THEN
@flag1='False';
END IF;
IF LENGTH(@mstxt)!=15 OR LENGTH(@mstxt)!=25 OR LENGTH(@mstxt)!=35 OR LENGTH(@mstxt)!=45 OR LENGTH(@mstxt)!=55 THEN
@flag1='False';
END IF;
IF @flag1='False' THEN
INSERT INTO er_message(r_mes) VALUES(CONCAT_WS(' ','编码错误',NULL,new.txt));
UPDATE r_message SET errType=2;
ELSE
SET @ccid=SUBSTR(@mstxt,1,4);/* 不需要转成int型*/
IF NOT EXISTS(SELECT * FROM customer WHERE cid=@ccid) THEN
INSERT INTO er_message(r_mes) VALUES(CONCAT_WS(' ','客户编码错误',NULL,new.txt));
UPDATE r_message SET errType=3;
ELSE
SET @ccname='';
SELECT cname INTO @ccname FROM customer WHERE cid=@ccid; /* 取出客户名*/
IF LENGTH(@mstxt)=15 THEN
SET @ppid=SUBSTR(@mstxt,6,5);
SET @ppcount=CONVERT( SUBSTR(@mstxt,12,3),INT);
SET @flag15='';
IF NOT EXISTS(SELECT * FROM product WHERE pid=@ppid) THEN
@flag15='False';
INSERT INTO er_message(r_mes) VALUES(CONCAT_WS(' ','产品编码错误',NULL,new.txt));
UPDATE r_message SET errType=4;
END IF;
IF @ppcount<1 OR @ppcount>999 THEN
@flag15='False';
INSERT INTO er_message(r_mes) VALUES(CONCAT_WS(' ','数量编码错误',NULL,new.txt));
UPDATE r_message SET errType=5;
END IF;
IF @flag15!='False' THEN
SET @ppname='';
SELECT pname INTO @ppname FROM product WHERE pid=@ppid; /* 取出商品名*/
INSERT INTO r_order(eid,ename,cid,cname,pid,pname,pcount,MID)VALUES(@eeid,@eename,@ccid,@ccname,@ppid,@ppname,@pcount,new.mid);
UPDATE r_message SET errType=1;
END IF;
END IF;
/*
IF LENGTH(@mstxt)=25 THEN
END IF;
IF LENGTH(@mstxt)=35 THEN
END IF;
IF LENGTH(@mstxt)=45 THEN
END IF;
IF LENGTH(@mstxt)=55 THEN
END IF;
*/
END IF;
END IF;
END IF;
END;
$$
DELIMITER ;
mysql> select * from r_order;
+-----+------+--------+------+----------+-------+--------+--------+------+
| rid | eid | ename | cid | cname | pid | pname | pcount | mid |
+-----+------+--------+------+----------+-------+--------+--------+------+
| 1 | 1 | test01 | 1001 | 芊芊超市 | 10001 | 矿泉水 | 1 | 0 |
| 2 | 1 | test01 | 1001 | 芊芊超市 | 10001 | 矿泉水 | 1 | 0 |
+-----+------+--------+------+----------+-------+--------+--------+------+
2 rows in set (0.00 sec)
DELIMITER $$
USE `test`$$
DROP TRIGGER /*!50032 IF EXISTS */ `ana_trigger`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `ana_trigger` AFTER INSERT ON `r_message`
FOR EACH ROW BEGIN
IF NOT EXISTS(SELECT * FROM employee WHERE phone=SUBSTR(new.phone,3,11) ) THEN
INSERT INTO er_message(r_mes) VALUES(CONCAT_WS(' ','您非本公司员工,无权完成此操作',NULL,new.txt));
UPDATE r_message SET errType=6;
ELSE
SET @eeid=1;
SET @eename='';
SET @mstxt=new.txt;
SET @flag1='';
SELECT eid INTO @eeid FROM employee WHERE phone=SUBSTR(new.phone,3,11) ;
SELECT ename INTO @eename FROM employee WHERE phone=SUBSTR(new.phone,3,11) ;
IF new.txt NOT REGEXP '^[0-9]*#[0-9]*#[0-9]*#' THEN
SELECT 'False' INTO @flag1;
END IF;
IF LENGTH(@mstxt)!=15 OR LENGTH(@mstxt)!=25 OR LENGTH(@mstxt)!=35 OR LENGTH(@mstxt)!=45 OR LENGTH(@mstxt)!=55 THEN
SELECT 'False' INTO @flag1;
END IF;
IF @flag1='False' THEN
INSERT INTO er_message(r_mes) VALUES(CONCAT_WS(' ','编码错误',NULL,new.txt));
UPDATE r_message SET errType=2;
ELSE
SET @ccid=SUBSTR(@mstxt,1,4);/* 不需要转成int型*/
IF NOT EXISTS(SELECT * FROM customer WHERE cid=@ccid) THEN
INSERT INTO er_message(r_mes) VALUES(CONCAT_WS(' ','客户编码错误',NULL,new.txt));
UPDATE r_message SET errType=3;
ELSE
SET @ccname='';
SELECT cname INTO @ccname FROM customer WHERE cid=@ccid; /* 取出客户名*/
IF LENGTH(@mstxt)=15 THEN
SET @ppid=SUBSTR(@mstxt,6,5);
SET @ppcount=CONVERT( SUBSTR(@mstxt,12,3),SIGNED);
SET @flag15='';
IF NOT EXISTS(SELECT * FROM product WHERE pid=@ppid) THEN
SELECT 'False' INTO @flag15;
INSERT INTO er_message(r_mes) VALUES(CONCAT_WS(' ','产品编码错误',NULL,new.txt));
UPDATE r_message SET errType=4;
END IF;
IF @ppcount<1 OR @ppcount>999 THEN
SELECT 'False' INTO @flag15;
INSERT INTO er_message(r_mes) VALUES(CONCAT_WS(' ','数量编码错误',NULL,new.txt));
UPDATE r_message SET errType=5;
END IF;
IF @flag15!='False' THEN
SET @ppname='';
SELECT pname INTO @ppname FROM product WHERE pid=@ppid; /* 取出商品名*/
INSERT INTO r_order(eid,ename,cid,cname,pid,pname,pcount,MID)VALUES(@eeid,@eename,@ccid,@ccname,@ppid,@ppname,@pcount,new.mid);
UPDATE r_message SET errType=1;
END IF;
END IF;
/*
IF LENGTH(@mstxt)=25 THEN
END IF;
IF LENGTH(@mstxt)=35 THEN
END IF;
IF LENGTH(@mstxt)=45 THEN
END IF;
IF LENGTH(@mstxt)=55 THEN
END IF;
*/
END IF;
END IF;
END IF;
END;
$$
DELIMITER ;
mysql> insert into r_message(txt,phone) values('1001#10001#001#','8613525632356'
);
ERROR 1442 (HY000): Can't update table 'r_message' in stored function/trigger be
cause it is already used by statement which invoked this stored function/trigger
.
@flag1='False';
这种形式 为: SELECT 'False' INTO @flag1;
Error Code : 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 'int);
set @flag15='';
' at line 38