56,679
社区成员
发帖
与我相关
我的任务
分享
drop table if exists orders,NextSerialNr;
create table orders(orders_id varchar(12) primary key,customer_name varchar(100) );
create table NextSerialNr(DT date,NextNr int);
delimiter $$
CREATE TRIGGER tr_orders_id BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
declare n int;
select NextNr into n from NextSerialNr where DT=DATE_FORMAT(CURDATE(),'%Y%m%d');
if n is null then
begin
insert into NextSerialNr values(CURDATE(),2);
set n=1;
end;
end if;
set NEW.orders_id=concat(DATE_FORMAT(CURDATE(),'%Y%m%d'),right(10000+n,4));
END;
$$
delimiter ;
insert into orders(customer_name) value('jack');
insert into orders(customer_name) value('jason');
select * from orders;