将记录从一个表移动到另一个表 [英] Move a record from one table to another

问题描述

大家好，

我想知道哪种方法最适合我的情况.
我有一张表，其中包含有关订单的信息以及一个指示订单状态的字段(字段的类型为smallint).我使用存储过程来更改状态.当订单的状态变为例如10(表示订单已完成)时，我想将此行移至另一个表并将其从订单表中删除.哪个更好:

Hi guys,

I''m wondering which is the best approach in my situation.
I have a table with information about the orders and a field which indicates the status of the order(the type of the field is smallint). I use a stored procedure to change the status. When the status of the order becomes, for example 10 (it indicates that the order is completed), I want to move this row to the another table and delete it from the table of orders. Which is better:

INSERT INTO <TABLE> SELECT * FROM

，然后

DELETE FROM ...

或

触发器(INSTEAD OF UPDATE)-它将监视状态，但是我认为将业务逻辑纳入触发器不是一个好主意

如果您还有其他建议，请与我分享.提前谢谢！

最好的问候，
xclus

Or

TRIGGER (INSTEAD OF UPDATE) - it will monitor the status, but I think that it''s not a good idea to have business logic into the trigger

If you have other suggestions, please share with me. Thanks in advance!

Best regards,
xclus

推荐答案

无论哪种方法都可以，但是如果您认为触发业务逻辑不好，我建议这样做.

在SP中:

Hi,

Whichever will do but if you think that Business Logic is not good to be in trigger, I would suggest this.

in SP:

if varStatus = 10 then
  insert into TABLE select from ... where CONDITION
  delete from ... where CONDITION
else
  update TABLE
  set status = 10
  where CONDITION
endif

问候，

恩南
:)

Regards,

Enan
:)

[删除]
我建议您使用触发器，因为如果您为此场景编写自己的逻辑，那么您就必须注意所有可能的异常情况.

您可以在触发器中使用条件"将信息从一个位置移动到另一位置.您还需要事务来检查您的流程是否作为单个流程执行.
[/删除]


提出此解决方案后，我读到您已经存储了更新状态的存储过程，在这种情况下，只需在StoredProcedure中使用该移动行逻辑即可，但不要忘记使用Transaction.
[/编辑]

谢谢
-Amit Gajjar

Hi,

[Remove]
I suggest you to go for the trigger, because if you write your own Logic for this scenario then you have to taken care of all possible exception scenarios.

You can use Condition in your trigger to move your information from one place to another. you also need transaction to check if your process is executed as single process.
[/Remove]


After positing this solution i read that you have stored procedure to update status,In that case just use that moving row logic in your StoredProcedure but don''t forget to use Transaction.
[/Edit]

Thanks
-Amit Gajjar

我认为使用触发器(不是触发器而不是触发器)或编写存储过程即可.

但是，我不会首先将行从表移动到另一个表.

在我看来，根据数据移动行会为应用程序引入额外的逻辑，通常这不是必需的.例如，发生以下情况时会发生什么情况:
-状态来回改变
-无论状态如何，您都希望列出所有订单
-引入了新的状态等.

如果您担心性能，可以通过使用适当的索引和编写良好的SQL语句来解决.同样，如果愿意，您可以在表上写视图以根据预定义的条件(例如状态)来限制结果.

I think using a trigger (not an instead of trigger) or writing a stored procedure would do the job.

However, I wouldn''t move the rows from a table to another in the first place.

In my opinion moving the rows based on the data introduces extra logic to the application, which typically isn''t necessary. For example what happens when:
- the status changes forth and back
- you want to list all the orders regardless of the status
- a new status is introduced etc.

If you''re worried about the performance, it can be tackled by using proper indexes and well written SQL statements. Also if you like, you can write views on the table to limit the results based on a predefined conditions like the status.

这篇关于将记录从一个表移动到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！