触发器

[wodezhuai]

按照时间来获取编码

比如在Order表里 有OrderId char（10）、OrderDate datetime

在添加列表时只输入OrderDate 来自动获取 OrderId

比如OrderDate是2008-01-01 而编码 就是2008010101
如果OrderDate还是2008-01-01 而编码 就是2008010102

如果表里面已经有日期为2008-01-03 最大的编码是2008010306
而添加时 日期还是2008-01-03 而编码就是在最大的上面加1 就是2008010307

急求一个触发器 谢谢

[wodezhuai]

这是我自己写的触发器 但是有问题
CREATE TRIGGER TR_OrderInfo ON Tb_Order FOR INSERT
AS

declare @OrderDate datetime
declare @FlowId int
declare @Ordertime varchar
declare @FlowIdChar varchar(10)

select @OrderDate =OrderDate
from INSERTED

set @Ordertime = (select convert(varchar(8),cast(OrderDate as datetime),112) from Tb_Order WHERE orderdate=@OrderDate)

select @FlowId =MAX( substring(OrderId,9,2))
from Tb_Order
--where OrderDate = @OrderDate
set @FlowId = @FlowId + 1


if len(@FlowId) = 1
set @FlowIdChar = '0' + cast(@FlowId as varchar)
if len(@FlowId) = 2
set @FlowIdChar = cast(@FlowId as varchar)


update Tb_Order
set OrderId = @Ordertime + @FlowIdChar
where OrderDate = @OrderDate

[wodezhuai]

这是我自己写的触发器 但是有问题
CREATE TRIGGER TR_OrderInfo ON Tb_Order FOR INSERT
AS

declare @OrderDate datetime
declare @FlowId int
declare @Ordertime varchar
declare @FlowIdChar varchar(10)

select @OrderDate =OrderDate
from INSERTED

set @Ordertime = (select convert(varchar(8),cast(OrderDate as datetime),112) from Tb_Order WHERE orderdate=@OrderDate)

select @FlowId =MAX( substring(OrderId,9,2))
from Tb_Order
--where OrderDate = @OrderDate
set @FlowId = @FlowId + 1


if len(@FlowId) = 1
set @FlowIdChar = '0' + cast(@FlowId as varchar)
if len(@FlowId) = 2
set @FlowIdChar = cast(@FlowId as varchar)


update Tb_Order
set OrderId = @Ordertime + @FlowIdChar
where OrderDate = @OrderDate

[wodezhuai]

请大家帮帮忙