这可以使用SQL Server触发器实现吗？ [英] Is this can be achieved using SQL server triggers?

问题描述

嗨;



例如，我在SQL Server数据库中有3个表。像这样; 数据库方案



当一个新行添加到'OutgoingStockProducts'时表，StockCode表中的数量将在'Stock'表中减少。

当从'OutgoingStockProducts'表中删除一行时，StockCode的'Stock'表中的数量会增加。



我可以做这些通过使用SQL触发器。



但我怎么能这样做;当EntryDate在'OutgoingStockInfoForms'表中设置时，所有关联的'OutgoingStockProducts'的数量由StockCode在'Stock'表中获取并增加？



我有什么尝试过：



我认为这可以通过使用游标来实现，但这不是我认为的好解决方案，而且我真的不想使用游标。

Hi;

For example I have 3 tables in a SQL Server database. Like this; Database Scheme

When a new row added to the 'OutgoingStockProducts' table, the Quantity will decrease in 'Stock' table by StockCode.
When a row deleted from the 'OutgoingStockProducts' table, the Quantity will increase in 'Stock' table by StockCode.

I can do these by using SQL Trigger.

But how can I do this; When the EntryDate setted in 'OutgoingStockInfoForms' table all associated 'OutgoingStockProducts' 's quantites taken and increased in 'Stock' table by StockCode ?

What I have tried:

I think this can be achieved by using cursors but this is not a good solution I think, and I really don't want to use cursors.

推荐答案

您似乎熟悉触发器，但以下是一些您可能会觉得有用的文章：

触发器 - SQL Server [ ^ ]

SQL Server数据库触发器概述 [ ^ ]

在编写SQL触发器时​​要非常小心 [ ^ ]



你提到过使用游标，但不是为什么你可能会认为你需要一个 - 但是你需要做的就是加入触发器中的 Inserted 表...例如类似这样的事情

You appear to be familiar with triggers but just in case here are a few articles you might find useful:
Triggers -- SQL Server[^]
Overview of SQL Server database Triggers[^]
Be Very Careful When You Write SQL Trigger[^]

You have mentioned using cursors but not why you might think you would need one - however all you need to do is join to the Inserted table within the trigger ... e.g. something like this perhaps

UPDATE A
SET A.Quantity = A.Quantity + C.Quantity
FROM OutgoingStockProducts A
INNER JOIN Inserted B ON A.FormID = B.FormID
INNER JOIN Stock C ON A.StockCode = C.StockCode

如果这是偏离标记然后在下面评论，我会尝试进一步帮助

If this is way off the mark then comment below and I will try to help further

这篇关于这可以使用SQL Server触发器实现吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！