请教一个函数如何写？分店铺判断并减少库存及店铺库存。

[莫鸣]

CREATE TABLE [dbo].[cangku] (
[id] [int] NULL ,
[cangku] [int] NULL ,
[tmpA] [int] NULL ,
[tmpB] [int] NULL ,
[inPrice] [money] NULL ,
[allNum] [int] NULL ,
[allprice] [money] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[users] (
[id] [int] NULL ,
[u_Nam] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[u_dp] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[sale] (
[id] [int] NULL ,
[u_Nam] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[sale_num] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[sale_price] [money] NULL ,
[all_price] [money] NULL
) ON [PRIMARY]
GO

cangku 代表仓库，tempA，是店A，tempB 是店B，仓库表中有总仓，A店仓库和B店仓库，当tempA的用户销售产品，就减少A店及仓库数量。
当tempB的用户销售产品，就减少B店及仓库数量。
我想写一个函数，如何去实现这样子的呢？请教一下知情的朋友。

[DBA_磊仔]

你没有描述你这三个表的关系，而且库存表和销售表也没有表示商品的字段，我只能理解这只卖一样东西， 仓库也只有一条记录

create trigger tri_sale on [dbo].[sale]
for insert, update, delete
as
if @@rowcount <= 0 return --无影响的行则直接返回
if exists(select * from inserted) and not exists(select * from deleted)
begin --往sale表插入数据时
	update cangku set tmpA = a.tmpA - isnull(b.sale_num,0), allNum = a.allNum - b.sale_num
		from cangku a,inserted b, users c where b.u_Nam = c.u_Nam and c.u_dp = 'tempA'
	update cangku set tmpB = a.tmpB - isnull(b.sale_num,0), allNum = a.allNum - b.sale_num
		from cangku a,inserted b, users c where b.u_Nam = c.u_Nam and c.u_dp = 'tempB'
end
if exists(select * from inserted) and exists(select * from deleted) and update(sale_num)
begin --更新sale表的销售数量时
	update cangku set tmpA = a.tmpA - isnull(b.sale_num,0) + isnull(d.sale_num,0), allNum = a.allNum - b.sale_num
		from cangku a,inserted b, users c, delted d where b.u_Nam = c.u_Nam and b.id = d.id and c.u_dp = 'tempA' 
	update cangku set tmpB = a.tmpB - isnull(b.sale_num,0) + isnull(d.sale_num,0), allNum = a.allNum - b.sale_num
		from cangku a,inserted b, users c, delted d where b.u_Nam = c.u_Nam and b.id = d.id and c.u_dp = 'tempB'
end
if not exists(select * from inserted) and exists(select * from deleted)
begin --删除sale表的数据时
	update cangku set tmpA = a.tmpA + isnull(d.sale_num,0), allNum = a.allNum - b.sale_num
		from cangku a, users c, delted d where d.u_Nam = c.u_Nam and b.id = d.id and c.u_dp = 'tempA' 
	update cangku set tmpB = a.tmpB + isnull(d.sale_num,0), allNum = a.allNum - b.sale_num
		from cangku a, users c, delted d where d.u_Nam = c.u_Nam and b.id = d.id and c.u_dp = 'tempB'
end

[莫鸣]

这三个表是一个仓存表，一个是用户表，一个是销售表 当用户在店铺A卖产品的时候，仓库中就减少A店（在仓存表中的tempA）的数据和cangku 的数据。 同理，在店铺B卖产品，就建行骚B店的数据。用户分A店=tempA，B店=tempB 下面是数据，谢谢。

insert into cangku values('1001',30,40,30,20,100,2000)
insert into cangku values('1002',5,30,20,20,55,1100)
insert into cangku values('1003',11,12,27,35,50,1650)

insert into users values('1','moses','tempA')
insert into users values('2','julia','tempB')

insert into sale  values('1001','julia',2,25,50);
insert into sale  values('1001','julia',3,28,84);
insert into sale  values('1001','julia',6,25,50);
insert into sale  values('1001','moses',8,45,360);

[莫鸣]

不好意思，下面一个表插入的数据错了。奇怪csdn不能编辑数据。 销售表的数据应该是这样子的插入：

insert into sale  values('1001','julia',2,25,50);
insert into sale  values('1002','julia',3,28,84);
insert into sale  values('1002','julia',6,25,50);
insert into sale  values('1003','moses',8,45,360);

[DBA_磊仔]

创建上面那个触发器，在销售数据插入sale表时，会自动维护cangku表的库存