declare @tb1 table(sid int,num int)
insert into @tb1 select '1',20
insert into @tb1 select '2',30
select * from @tb1declare @tb2 table(sid int,num int)
insert into @tb2 select '1',100
insert into @tb2 select '2',100
select * from @tb2如上两表,向@tb1中增加一条数据时,@tb2自动减去插入的数num数量
如 insert into @tb1 select '1',2
时
得到
@tb2
sid num
1 98
2 100
@tb1与@tb2中sid 一一对应
declare @tb1 table(sid int,num int)
insert into @tb1 select '1',20
insert into @tb1 select '2',30
select * from @tb1
declare @tb2 table(sid int,num int)
insert into @tb2 select '1',100
insert into @tb2 select '2',100
select * from @tb2insert into @tb1 select '1',2select * from @tb1
select * from @tb2-------------------------------(1 行受影响)(1 行受影响)
sid num
----------- -----------
1 20
2 30(2 行受影响)(1 行受影响)(1 行受影响)sid num
----------- -----------
1 100
2 100(2 行受影响)(1 行受影响)sid num
----------- -----------
1 20
2 30
1 2(3 行受影响)sid num
----------- -----------
1 100
2 100(2 行受影响)
for insert
as
update @tb2 set num=num-i.num
from @tb2 a,inserted i
where a.sid=i.sid
insert into tb1select '1',20
insert into tb1 select '2',30
select * from tb1create table tb2((sid int,num int)
insert into tb2 select '1',100
insert into tb2 select '2',100
select * from tb2在tb1上键触发器
create table tb1 (sid int,num int)
insert into tb1 select '1',20
insert into tb1 select '2',30
select * from tb1create table tb2 (sid int,num int)
insert into tb2 select '1',100
insert into tb2 select '2',100
select * from tb2create trigger tri_tb1_insert on tb1
after insert
as
set nocount on
if exists(select * from tb2 join inserted on tb2.sid=inserted.sid)
begin
update tb2 set tb2.num =tb2.num - inserted.num from tb2,inserted where tb2.sid=inserted.sidend
else
begin
insert into tb2 select sid,num*(-1) from insertedend
return
insert into tb1 select '1',2
insert into tb1 select '3',2
select * from tb2
sid num
----------- -----------
1 98
2 100
3 -2(3 行受影响)