如何根据其他表的值增加一个表的列？

I am making a project on the online shopping site. there i am having on table for product details and other for stock details

我正在网上购物网站上制作一个项目。我在桌子上有产品详细信息和其他库存详情

the two tables are like this

这两个表是这样的

ProductInfo
Column Name   Data Type   Constraint
ProductID     Int             Primary key , auto increment
ProductName   Varchar(100)  
SubCategoryID Int             Foreign key with ProductSubCategory
CompanyID     Varchar(20)     Foreign key with CompanyInfo
Price         Float 
Quantity      Int   
Description   Varchar(1000) 

ProductStock
Column Name Data Type     Constraint
StockID     Int         Primary key,auto increment
ProductID   Int         Foreign key with ProductInfo
Quantity    Int 
StockType   Varchar     Check(‘IN’,’OUT’)   
StockDate   Datetime

now initially i have kept the value of quantity of productinfo = 0 and want increment or decreement it on the basis of value of quantity of ProductStock depending upon StockType

现在我最初保留了productinfo = 0的数量值，并希望根据StockType的数量值增加或减少它，具体取决于StockType

If stock is 'In' then increment the quantity of productinfo by the number=value of quantity of ProductStock

如果库存为'In'，则将productinfo的数量增加数量= ProductStock的数量值

If stock is 'Out' then decrement the quantity of productinfo by the number=value of quantity of ProductStock

如果库存为“Out”，则将productinfo的数量减去数量= ProductStock数量的值

What query should i made to made this operation?

我应该做什么查询来进行此操作？

2 个解决方案

#1

Try this,

尝试这个，

Just you need to update the ProductInfo table, when you insert the products in ProductStock table. When the stock is decrements in ProductStock table just put update productInfo table for the column name Quantity. For that match the ProductID on both tables.

只需在ProductStock表中插入产品时更新ProductInfo表。当ProductStock表中的库存减少时，只需将更新的productInfo表作为列名称Quantity。对于那个匹配两个表上的ProductID。

#2

Use a trigger "AFTER INSERT" to increment-decrements the desired values.

使用触发器“AFTER INSERT”递增 - 递减所需的值。

http://msdn.microsoft.com/en-us/library/ms189799(v=sql.100).aspx

#1