日期:2014-05-18 浏览次数:20431 次
怎么出售,是在出售后向销售表中插入一条记录吗?是的话 create trigger test_insert on test.dbo.销售表 for insert as update A set A.库存单价=C.库存单价 from 销售表 as A inner join inserted as B on A.产品名称=B.产品名称 inner join 库存表 as C on A.产品名称=C.产品名称; update A set A.库存数量=A.库存数量-B.出货数量, A.库存金额=A.库存数量*A.库存单价 from 库存表 as A inner join inserted as B on A.产品名称=B.产品名称 go
------解决方案--------------------
use hell
go
create table table_stock
(
product_name nchar(20) not null,
stock_amount int not null,
stock_price int not null default 'null',
[sum] as stock_amount*stock_price
constraint Uni_table_stock unique (product_name,stock_price)
)
go
create table table_sales
(
product_name nchar(20) not null,
sales_amount int not null,
sales_price int not null,
stock_price int null,
constraint FK_table_sales_stock_price foreign key (product_name,stock_price) references table_stock(product_name,stock_price)
)
go
create trigger tri_sales
on table_sales
after insert
as
begin
update a set [a].[stock_price]=[b].[stock_price] from table_sales as a,table_stock as b
where a.product_name=b.product_name
update b set b.stock_amount-=a.sales_amount from table_sales as a,table_stock as b
where a.product_name=b.product_name
end
go