27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE FUNCTION dbo.f_getdatetag_y(@tdate datetime) RETURNS float
AS
BEGIN
DECLARE @r1 float
DECLARE @r2 float
select @r1= (select top 1 isnull(标识号,0) from store_y where convert(datetime, convert(varchar(10),时间,120))= convert(datetime, convert(varchar(10),@tdate,120)) order by 标识号 desc)
if isnull(@r1,0)>0
set @r2 =@r1+ 0.00001
Else
set @r2 =convert(float,convert(varchar(8),@tdate,112))+ 0.00001
RETURN @r2
End
insert into store_y(单号,产品编码,数量,单价,时间,备注,标识号) select 入库单号,产品编码,数量,单价日期,备注, dbo.f_getdatetag_y(getdate() from ruku_y_pj where 入库单号='F20160128-0001'
if object_id('T') is not null
drop table T
go
create table T(id DECIMAL(13, 5) primary key,name nvarchar(10),时间 datetime)
go
--用触发器完成
create trigger tr_storey_insert on store_y
instead of insert
as
begin
declare @i int
DECLARE @r1 DECIMAL(13, 5)
DECLARE @r2 DECIMAL(13, 5)
DECLARE @tdate datetime
select * into # from inserted
select @tdate= (select top 1 时间 from #)
select @r1= (select top 1 标识号 from store_y where convert(datetime, convert(varchar(10),时间,120))= convert(datetime, convert(varchar(10),@tdate,120)) order by 标识号 desc)
set @r2=convert(DECIMAL(8,0),convert(varchar(8),@tdate,112))
if isnull(@r1,0)=0
set @r1 = 0.00001
else
set @r1 = @r1-convert(DECIMAL(8,0),convert(varchar(8),@tdate,112))
update # set id=@r2+@r1,@r1=@r1+ 0.00001
insert store_y select * from #
end
go
INSERT INTO store_y
( 单号 ,
产品编码 ,
数量 ,
单价 ,
时间 ,
备注 ,
标识号
)
SELECT 入库单号 ,
产品编码 ,
数量 ,
单价日期 ,
备注 ,
dbo.f_getdatetag_y(GETDATE())+(ROW_NUMBER()OVER(ORDER BY GETDATE())-1)*0.00001
FROM ruku_y_pj
WHERE 入库单号 = 'F20160128-0001';
CREATE TRIGGER tr_cstore_y ON store_y
INSTEAD OF INSERT
AS
BEGIN
DECLARE @tdate DATETIME
,@r1 DECIMAL(13, 5)
SET @tdate = GETDATE()
SELECT @r1 = ISNULL(MAX(标识号), CONVERT(VARCHAR(8), @tdate, 112))
FROM store_y WITH ( NOLOCK )
WHERE CONVERT(DATETIME, CONVERT(VARCHAR(10), 时间, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(10), @tdate, 120))
SELECT 单号
,产品编码
,数量
,单价
,时间
,备注
,标识号 = @r1 + ROW_NUMBER() OVER ( ORDER BY @tdate ) * 0.00001
FROM inserted
END
CREATE FUNCTION dbo.f_getdatetag_y ( @tdate DATETIME )
RETURNS DECIMAL(13, 5)
AS
BEGIN
DECLARE @r1 DECIMAL(13, 5)
DECLARE @r2 DECIMAL(13, 5)
SELECT @r1 = ( SELECT TOP 1
ISNULL(MAX(标识号),
CONVERT(VARCHAR(8), @tdate, 112))
FROM store_y WITH(NOLOCK)
WHERE CONVERT(DATETIME, CONVERT(VARCHAR(10), 时间, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(10), @tdate, 120))
)
SET @r2 = @r1 + 0.00001
RETURN @r2
END
函数改改,别用浮点类型