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]
GOCREATE 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]
GOCREATE 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]
GOcangku 代表仓库,tempA,是店A,tempB 是店B,仓库表中有总仓,A店仓库和B店仓库,当tempA的用户销售产品,就减少A店及仓库数量。
当tempB的用户销售产品,就减少B店及仓库数量。
我想写一个函数,如何去实现这样子的呢?请教一下知情的朋友。
[id] [int] NULL ,
[cangku] [int] NULL ,
[tmpA] [int] NULL ,
[tmpB] [int] NULL ,
[inPrice] [money] NULL ,
[allNum] [int] NULL ,
[allprice] [money] NULL
) ON [PRIMARY]
GOCREATE 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]
GOCREATE 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]
GOcangku 代表仓库,tempA,是店A,tempB 是店B,仓库表中有总仓,A店仓库和B店仓库,当tempA的用户销售产品,就减少A店及仓库数量。
当tempB的用户销售产品,就减少B店及仓库数量。
我想写一个函数,如何去实现这样子的呢?请教一下知情的朋友。
仓库也只有一条记录
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
同理,在店铺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);
销售表的数据应该是这样子的插入:
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);