/*==============================================================*/
/* Table: FETCHPAPER 创建语句 */
/*==============================================================*/
drop table if exists FETCHPAPER;
create table FETCHPAPER
(
ID int not null auto_increment comment '实名制领料单ID',
WAREHOUSEID int comment '仓库ID',
GOODSID int,
NUM varchar(64) comment '实名制领料单编号',
primary key (ID)
);/*==============================================================*/
/* 插入数据 */
/*==============================================================*/
insert into fetchpaper(WAREHOUSEID,GOODSID,NUM) values(12,23,'0391201002020000');
insert into fetchpaper(WAREHOUSEID,GOODSID,NUM) values(12,23,'0391201002020001');
insert into fetchpaper(WAREHOUSEID,GOODSID,NUM) values(12,23,'0391201002020002');
insert into fetchpaper(WAREHOUSEID,GOODSID,NUM) values(12,23,'0391201002020003');/*==============================================================*/
/* 想要得到的结果 */
/*==============================================================*/+----+-------------+---------+------------------+
| ID | WAREHOUSEID | GOODSID | NUM |
+----+-------------+---------+------------------+
| 1 | 12 | 23 | 0391201002020000 |
| 2 | 12 | 23 | 0391201002020001 |
| 3 | 12 | 23 | 0391201002020002 |
| 4 | 12 | 23 | 0391201002020003 |
+----+-------------+---------+------------------+
/* Table: FETCHPAPER 创建语句 */
/*==============================================================*/
drop table if exists FETCHPAPER;
create table FETCHPAPER
(
ID int not null auto_increment comment '实名制领料单ID',
WAREHOUSEID int comment '仓库ID',
GOODSID int,
NUM varchar(64) comment '实名制领料单编号',
primary key (ID)
);/*==============================================================*/
/* 插入数据 */
/*==============================================================*/
insert into fetchpaper(WAREHOUSEID,GOODSID,NUM) values(12,23,'0391201002020000');
insert into fetchpaper(WAREHOUSEID,GOODSID,NUM) values(12,23,'0391201002020001');
insert into fetchpaper(WAREHOUSEID,GOODSID,NUM) values(12,23,'0391201002020002');
insert into fetchpaper(WAREHOUSEID,GOODSID,NUM) values(12,23,'0391201002020003');/*==============================================================*/
/* 想要得到的结果 */
/*==============================================================*/+----+-------------+---------+------------------+
| ID | WAREHOUSEID | GOODSID | NUM |
+----+-------------+---------+------------------+
| 1 | 12 | 23 | 0391201002020000 |
| 2 | 12 | 23 | 0391201002020001 |
| 3 | 12 | 23 | 0391201002020002 |
| 4 | 12 | 23 | 0391201002020003 |
+----+-------------+---------+------------------+
解决方案 »
- [急] mysql的通用日志不好用
- MySQL如何按照日期字段查询?
- 如何在sqlyog中查看sql语句
- 请问mysql的 offset应该怎么写?
- ib2,ib3,ib4是干什么用的?
- 请问,如何把erwin 设计出来的表结构导成mysql可以用的sql脚本?在线等啊(就这些分了)
- 如何才能修改character_set_client等参数为gbk?
- MySQL和ACCESS的比较,刚测试的,请懂MySQL的高手进来指点(100分解惑)
- 在mysql--front中不能编辑
- 菜鸟问题:关于insert into personInfo values(....)
- load data infile的局限性
- MYSQL触发器的问题,请高手解答
CREATE TRIGGER TIB_FETCHPAPER BEFORE
INSERT ON FETCHPAPER FOR EACH ROW
declare @row_count int(10),
@max_num char(50),
@zero_today char(50),
begin
set @zero_today =CONCAT('0391',DATE_FORMAT(curdate(),'%Y%m%d'),RPAD('0',4,'0'));
select @row_count=count(*) from fetchpaper;
if @row_count = 0 then
@max_num=@zero_today
else
select @max_num=max(num) from fetchpaper;
end if
if @max_num <@zero_today then
@max_num=@zero_today
end if
if NEW.num is null then
NEW.num=rpad((@max_num)+1),14,'0');
end if
end;
//
这是我新写的触发器,一直说我declare 那地方出错误。
多了一个逗号
-> INSERT ON FETCHPAPER FOR EACH ROW
-> begin -- move the BEGIN to block start position - ACMAIN
-> declare row_count int(10);
-> declare max_num char(50); -- move the @ prefix the var, @is the session var. - ACMAIN
-> declare zero_today char(50); -- change the comman , to semicolumn ; - ACMAIN
->
-> set zero_today =CONCAT('0391',DATE_FORMAT(curdate(),'%Y%m%d'),RPAD('0',4,'0'));
-> select count(*) into row_count from fetchpaper; -- select into ! - ACMAIN
-> if row_count = 0 then
-> SET max_num=zero_today ; -- add the SET and ; - ACMAIN
-> else
-> select max(num) into max_num from fetchpaper; -- select into ! - ACMAIN
-> end if ;
->
-> if max_num <zero_today then
-> set max_num=zero_today ; -- add the SET and ; - ACMAIN
-> end if ;
->
-> if NEW.num is null then
-> set NEW.num=rpad((max_num+1),14,'0'); -- add the SET and remove one ')'- ACMAIN
-> end if ;
-> end;
-> //
Query OK, 0 rows affected (0.11 sec)mysql>
DELIMITER $$USE `testa`$$DROP TRIGGER /*!50032 IF EXISTS */ `TIB_FETCHPAPER`$$CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `TIB_FETCHPAPER` BEFORE INSERT ON `fetchpaper`
FOR EACH ROW BEGIN
DECLARE ROW_COUNT INT(10);
DECLARE max_num CHAR(50);
DECLARE zero_today CHAR(50);
SET zero_today =CONCAT('0391',DATE_FORMAT(CURDATE(),'%Y%m%d'),RPAD('0',4,'0'));
SELECT COUNT(*) INTO ROW_COUNT FROM fetchpaper;
IF ROW_COUNT = 0 THEN
SET max_num=zero_today ;
ELSE
SELECT MAX(num) INTO max_num FROM fetchpaper;
END IF ;
IF max_num <zero_today THEN
SET max_num=zero_today ;
END IF ;
SET NEW.num=RPAD(CONCAT(LEFT(max_num,LENGTH(max_num)-4),RIGHT(CONCAT('0000',RIGHT(max_num,4)+1),4)),16,'0');
END;
$$DELIMITER ;