求SQL2000字段内容计算 (注列userdef1里面的都是加减乘除的)表saledetail
列userdef1 , userdef2
20*2-20*1-8 ,
20*2+20*1-8+6,
要求结果如下
列userdef1 , userdef2
20*2-20*1-8 , 12
20*2+20*1-8+6+13 ,71
列userdef1 , userdef2
20*2-20*1-8 ,
20*2+20*1-8+6,
要求结果如下
列userdef1 , userdef2
20*2-20*1-8 , 12
20*2+20*1-8+6+13 ,71
create table saledetail
(userdef1 varchar(30), userdef2 int)insert into saledetail(userdef1)
select '20*2-20*1-8' union all
select '20*2+20*1-8+6'
-- begin
declare @x varchar(30),@tsql varchar(6000)
declare ap scroll cursor for
select userdef1 from saledetailopen apfetch first from ap into @x
while(@@FETCH_STATUS<>-1)
begin
select @tsql='update saledetail set userdef2='+@x
+' where userdef1='''+@x+''' '
exec(@tsql)
fetch next from ap into @x
endclose ap
deallocate ap
-- end
-- 结果
select userdef1,userdef2 from saledetail/*
userdef1 userdef2
------------------------------ -----------
20*2-20*1-8 12
20*2+20*1-8+6 58(2 row(s) affected)
*/
CREATE TRIGGER vip_saledetail ON [dbo].[saledetail]
FOR INSERT ,UPDATE
AS
declare @billid int ,@itemno int
select @billid=billid,@itemno=itemno from inserted
-- begin
declare @x varchar(30),@tsql varchar(6000)
declare ap scroll cursor for
select userdef1 from saledetail
open ap
fetch first from ap into @x
while(@@FETCH_STATUS<>-1)
begin
---- select @tsql='update saledetail set userdef2='+@x
----- +' where userdef1='''+@x+''' ' ---- select @tsql='update saledetail set userdef2='+@x
----- +' where userdef1='''+@x+''' and billid=@billid and itemno=@itemno '
exec(@tsql)
fetch next from ap into @x
end
close ap
deallocate ap
-- end
create table saledetail
(userdef1 varchar(30), userdef2 int)-- 建触发器
create trigger tr_saledetail on saledetail
after insert,update
as
begin
if update(userdef1)
begin
declare @x varchar(30),@tsql varchar(6000)
declare ap scroll cursor for
select userdef1 from inserted
open ap
fetch first from ap into @x
while(@@FETCH_STATUS<>-1)
begin
select @tsql='update saledetail set userdef2='+@x
+' where userdef1='''+@x+''' '
exec(@tsql)
fetch next from ap into @x
end close ap
deallocate ap
end
end
go
-- 插入数据
insert into saledetail(userdef1)
select '20*2-20*1-8' union all
select '20*2+20*1-8+6+13' union all
select '1+2+3+4'
-- 结果
select userdef1,userdef2 from saledetail
/*
userdef1 userdef2
------------------------------ -----------
20*2-20*1-8 12
20*2+20*1-8+6+13 71
1+2+3+4 10(3 row(s) affected)
*/
太感谢您了,但是有点疑问因为saledetail销售出库单,出库单里面会有多个明细时,要不要指定更新本单据,billid,itemno??? 会不会也更新之前的单据了,造成重复计算!
保存单据,提示更新数据库失败:在关键字 'where' 附近有语法错误。
create table saledetail
( billid int,
itemno int,
userdef1 varchar(30),
userdef2 int)
-- 建触发器
create trigger tr_saledetail on saledetail
after insert,update
as
begin
if update(userdef1)
begin
declare @x varchar(30),@billid int,@itemno int, @tsql varchar(6000)
declare ap scroll cursor for
select billid,itemno,userdef1 from inserted
open ap
fetch first from ap into @billid,@itemno,@x
while(@@FETCH_STATUS<>-1)
begin
select @tsql='update saledetail set userdef2='+@x
+' where billid='+rtrim(@billid)+' and itemno='+rtrim(@itemno)
+' and userdef1='''+@x+''' '
exec(@tsql)
fetch next from ap into @billid,@itemno,@x
end
close ap
deallocate ap
end
end
go
-- 插入数据
insert into saledetail(billid,itemno,userdef1)
select 1001, 1, '20*2-20*1-8' union all
select 1001, 2, '20*2+20*1-8+6+13' union all
select 1003, 5, '1+2+3+4'
-- 结果
select billid,itemno,userdef1,userdef2 from saledetail/*
billid itemno userdef1 userdef2
----------- ----------- ------------------------------ -----------
1001 1 20*2-20*1-8 12
1001 2 20*2+20*1-8+6+13 71
1003 5 1+2+3+4 10(3 row(s) affected)
*/
create table saledetail
( billid int,
itemno int,
userdef1 varchar(30),
userdef2 int)
-- 建触发器
create trigger tr_saledetail on saledetail
after insert,update
as
begin
if update(userdef1)
begin
declare @x varchar(30),@billid int,@itemno int, @tsql varchar(6000)
declare ap scroll cursor for
select billid,itemno,ltrim(rtrim(userdef1)) from inserted
open ap
fetch first from ap into @billid,@itemno,@x
while(@@FETCH_STATUS<>-1)
begin
select @tsql='update saledetail set userdef2='+case when @x<>'' then @x else '0' end
+' where billid='+rtrim(@billid)+' and itemno='+rtrim(@itemno)
+' and ltrim(rtrim(userdef1))='''+@x+''' '
exec(@tsql)
fetch next from ap into @billid,@itemno,@x
end
close ap
deallocate ap
end
end
go
-- 插入数据
insert into saledetail(billid,itemno,userdef1)
select 1001, 1, '20*2-20*1-8' union all
select 1001, 2, '20*2+20*1-8+6+13' union all
select 1003, 5, '' union all --> userdef1内容为空
select 1003, 6, '1+2+3+4' union all
select 1003, 7, ' ' --> userdef1内容为空
-- 结果
select billid,itemno,userdef1,userdef2 from saledetail/*
billid itemno userdef1 userdef2
----------- ----------- ------------------------------ -----------
1001 1 20*2-20*1-8 12
1001 2 20*2+20*1-8+6+13 71
1003 5 0
1003 6 1+2+3+4 10
1003 7 0(5 row(s) affected)
*/
create table saledetail
( billid int,
itemno int,
userdef1 varchar(30),
userdef2 int)-- 建触发器
create trigger tr_saledetail on saledetail
after insert,update
as
begin
if update(userdef1)
begin
declare @x varchar(30),@y varchar(30),@billid int,@itemno int, @tsql varchar(6000)
declare ap scroll cursor for
select billid,itemno,ltrim(rtrim(userdef1)) from inserted
open ap
fetch first from ap into @billid,@itemno,@x
while(@@FETCH_STATUS<>-1)
begin
set xact_abort off
begin try
select @tsql='declare @z int; select @z=cast('+@x+' as int);'
exec(@tsql)
select @y=case when @x<>'' then @x else null end
end try
begin catch
select @y=null
end catch select @tsql='update saledetail set userdef2='+rtrim(@y)
+' where billid='+rtrim(@billid)+' and itemno='+rtrim(@itemno)
+' and ltrim(rtrim(userdef1))='''+@x+''';'
exec(@tsql)
fetch next from ap into @billid,@itemno,@x
end
close ap
deallocate ap
end
end
go
-- 插入数据
insert into saledetail(billid,itemno,userdef1)
select 1001, 1, '20*2-20*1-8' union all
select 1001, 2, '20*2+20*1-8+6+13' union all
select 1003, 5, '' union all --> userdef1内容为空
select 1003, 6, '1+2+3+4' union all
select 1003, 7, ' ' union all --> userdef1内容为空
select 1003, 8, '1+-*/2+3' union all --> userdef1填写的内容不规范
select 1003, 9, '(1+2))+3' union all --> userdef1填写的内容不规范
select 1005, 1, '1+2*3'
-- 结果
select billid,itemno,userdef1,userdef2 from saledetail/*
billid itemno userdef1 userdef2
----------- ----------- ------------------------------ -----------
1001 1 20*2-20*1-8 12
1001 2 20*2+20*1-8+6+13 71
1003 5 NULL
1003 6 1+2+3+4 10
1003 7 NULL
1003 8 1+-*/2+3 NULL
1003 9 (1+2))+3 NULL
1005 1 1+2*3 7(8 row(s) affected)
*/