在C＃中更新查询。 [英] Update query in C# .

问题描述

我有两张桌子股票和发票

股票SR_NO是发票中的FK



我想更新股票表时发票表保存



当数量输入发票时库存数量将更新



我尝试过：



UPDATE T1

SET T1.Qty = T1.Qty - T2.Qty

FROM Stock T1

INNER JOIN G_Invoice T2

ON T1.SR_NO = T2.SR_NO

I have Two tables Stock and Invoice
Stock SR_NO is FK in Invoice

I want to update the table of stock when the invoice table is save

when Quantity enter in the Invoice the Quantity in stock will update

What I have tried:

UPDATE T1
SET T1.Qty = T1.Qty - T2.Qty
FROM Stock T1
INNER JOIN G_Invoice T2
ON T1.SR_NO = T2.SR_NO

推荐答案

如果我正确理解了问题，并且您想更新Stock-table而不管发票的更新方式如何，我建议您考虑触发器。请参阅 CREATE TRIGGER（Transact-SQL） [ ^ ]



随着触发你可以在发票上插入/更新/删除时作出反应，并对其他表格进行必要的操作，例如股票。

If I understand the question correctly, and you want to update the Stock-table regardless how the invoice is updated, I would suggest considering a trigger. See CREATE TRIGGER (Transact-SQL)[^]

With the trigger you could react upon insert/update/delete on invoice and do the necessary operations for other tables such as stock.

如果我是你，如果可能的话，根据你的应用程序的要求，我会创建一个存储过程来进行这两个更改。在内部存储过程中，编写查询以更新发票表，然后更新库存表。



从我看来，这个解决方案比使用触发器更干净，更易于维护（只使用一个对象来包含代码），但这取决于你。



伪代码类似于：

Hi,

If I were you, if possible according to your application's requirements, I would create a stored procedure to make both changes. Inside stored procedure, write a query to update "Invoice table" and then "Stock Table".

This solution, from my view, is cleaner and maintainable (only use one object to contain code) than using triggers, but it's up to you.

The pseudocode would be similar to:

CREATE PROCEDURE YourProcedureName
    @param1 -- Your Params
    ...
    @paramN,
    @Quantity
AS
BEGIN
	SET NOCOUNT ON;

    /* Query to insert into G_Invoice */
    insert into G_Invoice 
    values(@param1,..,@paramN,...,@Quantity) /* Set your values */

    /* Query to update Stock */
    SET T1.Qty = T1.Qty - @Quantity
    FROM Stock T1
    INNER JOIN G_Invoice T2
    ON T1.SR_NO = T2.SR_NO
    where /* Set filters according to params */
	
END
GO

最后，您可以使用SQL命令，实体框架等从访问数据层调用存储过程。

Finally, you can call the stored procedure from your access data layer making use of SQL Commands, Entity Framework, etc.

这篇关于在C＃中更新查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！