如何编写sql代码 在更新入库出库表的同时更新库存表
入库表
productname productid provider buyingprice productin
商品名称 商品ID 供应商 进价 入库
薯片 1 百事 3.00 1000
出库表
productname productid provider saleprice productout
商品名称 商品ID 供应商 售价 出库
薯片 1 百事 5.00 500
库存表
productname productid provider margin profit
商品名称 商品ID 供应商 余量 盈利
第一第二章表都有了,sql里怎么写代码通过第一第二张表生成第三张表
如何编写sql代码 在更新入库出库表的同时更新库存表
入库表
productname productid provider buyingprice productin
商品名称 商品ID 供应商 进价 入库
薯片 1 百事 3.00 1000
出库表
productname productid provider saleprice productout
商品名称 商品ID 供应商 售价 出库
薯片 1 百事 5.00 500
库存表
productname productid provider margin profit
商品名称 商品ID 供应商 余量 盈利
第一第二章表都有了,sql里怎么写代码通过第一第二张表生成第三张表
如何编写sql代码 在更新入库出库表的同时更新库存表
create table instock(pid int, num int)
go
create table stock(pid int, name varchar(10), num int)
go
insert into stock values(100,'PA', 0)
go
create trigger tri_instock_ins
on instock after insert, delete, update
as
begin
with m as (
select pid, sum(num) num
from instock
where pid in (select pid from inserted union all select pid from deleted)
group by pid
)
update t set t.num = m.num from stock t, m where m.pid = t.pid
end
go
insert into instock values(100,20)
insert into instock values(100,30)
go
select * from stock
go
update instock set num = 40 where num = 20
go
select * from stock
go
delete instock where num = 40
go
select * from stock
go
drop table instock, stock
go
(1 行受影响)
pid name num
----------- ---------- -----------
100 PA 50(1 行受影响)
(1 行受影响)(1 行受影响)
pid name num
----------- ---------- -----------
100 PA 70(1 行受影响)
(1 行受影响)(1 行受影响)
pid name num
----------- ---------- -----------
100 PA 30(1 行受影响)
CREATE TABLE a
(
[productname] NVARCHAR(22) ,
[productid] INT ,
[provider] NVARCHAR(22) ,
[buyingprice] DECIMAL(18, 2) ,
[productin] INT
)
Insert a
select N'薯片',1,N'百事',3.00,1000CREATE TABLE b
(
[productname] NVARCHAR(22) ,
[productid] INT ,
[provider] NVARCHAR(22) ,
[saleprice] DECIMAL(18, 2) ,
[productout] INT
)
Insert b
select N'薯片',1,N'百事',5.00,500CREATE TABLE c
(
productname NVARCHAR(22) ,
productid INT ,
provider NVARCHAR(22) ,
margin INT ,
profi DECIMAL(18, 2)
)
INSERT INTO c
SELECT a.productname ,
a.productid ,
a.provider ,
SUM(a.productin - b.productout) AS margin ,
SUM(b.productout * ( b.saleprice - a.buyingprice )) AS profit
--根据实际情况改成表的名称
FROM a
JOIN b ON b.productid = a.productid
GROUP BY a.productname ,
a.productid ,
a.provider;出入库都加这个触发器(例子只给入库的加了。如果出库也需要重新计算的话也得加):
CREATE TRIGGER test_a ON a
AFTER INSERT, DELETE, UPDATE
AS
BEGIN UPDATE c
SET c.margin = t.margin ,
c.profi = t.profit
FROM c c
JOIN ( SELECT a.productid ,
SUM(a.productin - b.productout) AS margin ,
SUM(b.productout * ( b.saleprice
- a.buyingprice )) AS profit
FROM a
JOIN b ON b.productid = a.productid
WHERE a.productid IN ( SELECT productid
FROM inserted
UNION ALL
SELECT productid
FROM deleted )
GROUP BY a.productid
) t ON c.productid = t.productid
END
CREATE TRIGGER test_b ON b
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
UPDATE c
SET c.margin = t.margin ,
c.profi = t.profit
FROM c c
JOIN ( SELECT a.productid ,
SUM(a.productin - b.productout) AS margin ,
SUM(b.productout * ( b.saleprice
- a.buyingprice )) AS profit
FROM a
JOIN b ON b.productid = a.productid
WHERE a.productid IN ( SELECT productid
FROM inserted
UNION ALL
SELECT productid
FROM deleted )
GROUP BY a.productid
) t ON c.productid = t.productid
END