create trigger trg_test
on A
for insert
as
declare @isbn varchar(50)
select @isbn=isbn from inserted
--如果B表已经有此ISBN了,就更新此数据,让库存 数量累加
if(exists(select * from b where isbn=@isbn))
update B set B.stock_num=b.stock_num+(select in_num from inserted) where B.isbn=@isbn
else
--如果B表中无此ISBN,那么就在B表中添加此记录
insert B(isbn,stock_num) select isbn,in_num from inserted
-------------------------------------------------------------------------------
--测试
--建立表
create table A(id int identity(1,1),isbn varchar(50),in_num int)
create table B(id int identity(1,1),isbn varchar(50),stock_num int)
go
--建立触发器
create trigger trg_test
on A
for insert
as
declare @isbn varchar(50)
select @isbn=isbn from inserted
--如果B表已经有此ISBN了,就更新此数据,让库存 数量累加
if(exists(select * from b where isbn=@isbn))
update B set B.stock_num=b.stock_num+(select in_num from inserted) where B.isbn=@isbn
else
--如果B表中无此ISBN,那么就在B表中添加此记录
insert B(isbn,stock_num) select isbn,in_num from inserted
GO
--插入模拟数据
insert into A values('ISDN_001',10)
insert into A values('ISDN_002',10)
insert into A values('ISDN_003',10)
insert into A values('ISDN_001',10)
--查询
select * from B
--结果
/**
id isbn stock_num
1 ISDN_001 20
2 ISDN_002 10
3 ISDN_003 10