--测试表 create table ta ( id int, c1 int, c2 int, c3 int )--触发器 create trigger t_add on ta after update,insert as begin update a set c3 = isnull(b.c1,0) + isnull(b.c2,0) from ta a inner join inserted b on a.id = b.id end--测试数据1 - 插入 insert into ta select 1,1,1,1 /* id c1 c2 c3 ----------- ----------- ----------- ----------- 1 1 1 2(1 行受影响) */--测试数据2 - 再插入 insert into ta select 2,1,5,1/* id c1 c2 c3 ----------- ----------- ----------- ----------- 1 1 1 2 2 1 5 6(2 行受影响) */--测试数据3 - 更新 update ta set c2 = 10 /* id c1 c2 c3 ----------- ----------- ----------- ----------- 1 1 10 11 2 1 10 11(2 行受影响) */--删除测试表 drop table ta
用计算列不可以完成么 ?为何要用触发器 。。 create table et ( col1 int, col2 int, col3 as col1+col2 ) insert et select 1,2 union select 2,3select * from et update et set col1=col1+1 select * from et /* col1 col2 col3 ----------- ----------- ----------- 2 2 4 3 3 6 */
--测试表
create table ta
(
id int,
c1 int,
c2 int,
c3 int
)--触发器
create trigger t_add on ta
after update,insert
as
begin
update a set c3 = isnull(b.c1,0) + isnull(b.c2,0)
from ta a inner join inserted b on a.id = b.id
end--测试数据1 - 插入
insert into ta select 1,1,1,1
/*
id c1 c2 c3
----------- ----------- ----------- -----------
1 1 1 2(1 行受影响)
*/--测试数据2 - 再插入
insert into ta select 2,1,5,1/*
id c1 c2 c3
----------- ----------- ----------- -----------
1 1 1 2
2 1 5 6(2 行受影响)
*/--测试数据3 - 更新
update ta set c2 = 10
/*
id c1 c2 c3
----------- ----------- ----------- -----------
1 1 10 11
2 1 10 11(2 行受影响)
*/--删除测试表
drop table ta
create table et
(
col1 int,
col2 int,
col3 as col1+col2
)
insert et
select 1,2 union select
2,3select * from et update et
set col1=col1+1 select * from et
/*
col1 col2 col3
----------- ----------- -----------
2 2 4
3 3 6
*/