create trigger tri_CreateOrderNo
on [Order]
after insert
as
update [order] set orderNo=CONVERT(varchar(12),getdate(),112)+
right(cast(power(10,7) as varchar)+
(
select COUNT(*)+1 from [order] where OrderNo like CONVERT(varchar(12),getdate(),112)+‘%‘
),7)
from inserted i where i.orderId=[order].orderId
触发器生成订单号 格式为：201503260000001 年月日+(按天自增序号)
原文：http://www.cnblogs.com/yisic/p/4370461.html