此时着急做一些程序,暂时请这里的兄弟们帮忙想一下,晚上看答案,多谢各位了!!!需求:
一个数据表 T1
id,col1,col2,col3
一个参数表T2,里面有T1所有列的参考数据值的上限、下限的定义。
colname, L, H
col1 2.33 4.53
col2 14.7 23.3
col3 233 453
请帮忙写一个触发器,要求当向T1插入数据或者修改数据的时候,如果T1为0则自动生成一个符合规则的随机数。
一个数据表 T1
id,col1,col2,col3
一个参数表T2,里面有T1所有列的参考数据值的上限、下限的定义。
colname, L, H
col1 2.33 4.53
col2 14.7 23.3
col3 233 453
请帮忙写一个触发器,要求当向T1插入数据或者修改数据的时候,如果T1为0则自动生成一个符合规则的随机数。
这个要拼sql.比较麻烦.
..我觉得应该交给前台程序去做.
declare @ID int
declare @col1L dec
declare @col1H dec
declare @col2L dec
declare @col2H dec
declare @col3l dec
declare @col3H decselect
@ID = id,
@col1=col1,
@col2=col2,
@col3=col3
from insertedif @col1 ==0
select @col1 =L + (H-L)*rand() from T2 where colname='col1 'if @col2 ==0
select @col2 =L + (H-L)*rand() from T2 where colname='col2 ' if @col3 ==0
select @col3 =L + (H-L)*rand() from T2 where colname='col3 ' update T1
set col1=@col1,
col2=@col2,
col3=@col3
where id=@id
...............
没有测试,提供这样一个思路
create table T2(colname varchar(32), L decimal(5,2), H decimal(5,2))
insert T2 select 'col1', 2.33, 4.53
union all select 'col2', 14.7, 23.3
union all select 'col3', 233, 453 go
create trigger trig_Test_T1 on T1
for insert, update
as
begin
declare @cL1 int, @cH1 int, @cL2 int, @cH2 int, @cL3 int, @cH3 int
select @cL1=cast(L*10000 as int), @cH1=cast(H*10000 as int) from T2 where colname='col1'
select @cL2=cast(L*10000 as int), @cH2=cast(H*10000 as int) from T2 where colname='col2'
select @cL3=cast(L*10000 as int), @cH3=cast(H*10000 as int) from T2 where colname='col3' update T1
set col1=case T.col1 when 0 then (checksum(newid())%((@cH1-@cL1)/2)+(@cH1+@cL1)/2)/10000.0
else T.col1 end
, col2=case T.col2 when 0 then (checksum(newid())%((@cH2-@cL2)/2)+(@cH2+@cL2)/2)/10000.0
else T.col2 end
, col3=case T.col3 when 0 then (checksum(newid())%((@cH3-@cL3)/2)+(@cH3+@cL3)/2)/10000.0
else T.col3 end
from T1 T join inserted i on T.id = i.id
where i.col1=0 or i.col2=0 or i.col3=0
end
goinsert T1 select 1, 0, 0.0, 0
union all select 2, 2, 0, 2
union all select 3, null, null, nullselect * from T1
/*
id col1 col2 col3
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1 4.51 19.10 416.69
2 2.00 21.47 2.00
3 NULL NULL NULL(3 row(s) affected)
*/update T1 set col1=0, col2=0, col3=3 where id=3select * from T1
/*
id col1 col2 col3
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1 4.51 19.10 416.69
2 2.00 21.47 2.00
3 2.41 18.98 3.00(3 row(s) affected)
*/
drop trigger trig_Test_T1
drop table T1, T2
abs(checksum(newid()))更严谨点吧
for insert, update
as
set nocount on while exists (select 1 from T1,Inserted i where i.id=T1.id and T1.Col1=0)
begin
set rowcount 1
update T1 set Col1=cast(T2.L+(T2.H-T2.L)*Rand(900000*datepart(ms,getdate())) as numeric(10,2))
from T1,Inserted i,T2
where i.id=T1.id
and T1.Col1=0
and T2.colname='col1'
set rowcount 0
end while exists (select 1 from T1,Inserted i where i.id=T1.id and T1.Col2=0)
begin
set rowcount 1
update T1 set Col2=cast(T2.L+(T2.H-T2.L)*Rand(900000*datepart(ms,getdate())) as numeric(10,2))
from T1,Inserted i,T2
where i.id=T1.id
and T1.Col2=0
and T2.colname='col2'
set rowcount 0
end while exists (select 1 from T1,Inserted i where i.id=T1.id and T1.Col3=0)
begin
set rowcount 1
update T1 set Col3=cast(T2.L+(T2.H-T2.L)*Rand(900000*datepart(ms,getdate())) as numeric(10,2))
from T1,Inserted i,T2
where i.id=T1.id
and T1.Col3=0
and T2.colname='col3'
set rowcount 0
end
go