先把表添加一个标识id列
用触发器
create trigger test
on tablename
for update
as
update tablename
set c=b.a+b.b
from tablename a
inner join inserted b
on a.id=b.id
go
用触发器
create trigger test
on tablename
for update
as
update tablename
set c=b.a+b.b
from tablename a
inner join inserted b
on a.id=b.id
go
create function f_calc(@id int)
returns int
as
begin
return(isnull((select sum(a)+sum(b) from tb where id<@id),0))
end
go--将c字段设置为计算字段
create table tb(id int identity,a int,b int,c as dbo.f_calc(id))
go--插入数据测试
insert tb(a,b) select 1,1
union all select 2,2
union all select 2,3select * from tb
go--删除测试
drop table tb
drop function f_calc/*--测试结果id a b c
----------- ----------- ----------- -----------
1 1 1 0
2 2 2 2
3 2 3 6(所影响的行数为 3 行)--*/
create function dbo.fn_calc(@id int)
returns int
as
begin
return(isnull((select a+b from tb where id=@id),0))
end
go
--建立测试的表
create table tb(id int identity,a int,b int,c as dbo.fn_calc(id))
--插入数据
insert tb(a,b)
select 1,2
union select 3,4
union select 4,8
union select 3,8
--结果
select * from tb
/*
id a b c
----------- ----------- ----------- -----------
1 1 2 3
2 3 4 7
3 3 8 11
4 4 8 12(所影响的行数为 4 行)
*/
--建立计算列的表
create table tb(a int,b int,c as isnull(a+b,0))
--插入数据
insert tb(a,b)
select 1,2
union select 3,4
union select 4,8
union select 3,8
--结果
/*
a b c
----------- ----------- -----------
1 2 3
3 4 7
3 8 11
4 8 12(所影响的行数为 4 行)
*/