How to update related tables when we update a table?

am creating a C# Windows application which is based on a medical inventory.In this application I have mainly three forms as Pu开发者_StackOverflowrchaseDetail,SalesDetail,and StockDetail.

Now I want a functionality in which if I insert or modify the records in PurchaseDetail,or SalesDetail, the data in the StockDetail should also be modified.(for example if i insert some quantity of medicines in PurchaseDetail then Quantity in StockDetail should also modified and same as for SalesDetail )

Columns in PurchaseDetail: Id(Primary Key and auto increment int),BatchNumber,MedicineName,ManufacturingDate,ExpiryDate,Rate,MRP,Tax,Discount,Quantity

Columns in SalesDetail: Id(PrimaryKey and auto increment int),BillNumber,CustomerName,BatchNumber,Quantity,Rate,SalesDate

Columns in StockDetail: Id(Primary Key and auto increment int),ProductId,ProductName,OpeningStock,ClosingStock,PurchaseQty,DispenseQty,PurchaseReturn,DispenseReturn

Please help me.

Write a stored procedure that encapsulates the actions you need. Then call that procedure.

I would say a trigger would be the best solution here, as you basically want auditing functionality.

Depending on your business logic, I would strongly recommend that you avoid this kind of behavior where you change quantities whenever other values are added or changed.
Save all your transactions, either if they are adding or subtracting to your total quantity (positive or negative quantities), and sum all those transactions to get your totals.

In general, I'd steer clear of triggers - they're a pain to try and debug if nothing else.

Using the design principle of SRO (Single Object Responsibility) I think it's better design that stock managment takes up part of the business logic of your class, such that confirming a purchase should make an explicit call to the Stock Management class which then calls the database updating that table.

Clearly this should be done in a transaction to ensure that stock hasn't been entirely depleted by another user while the order was being keyed.