使用更新触发器将数据从一个表移动到另一个表

发布于 2024-09-18 22:31:53 字数 393 浏览 2 评论 0原文

我是数据库开发的新手。请帮助我创建一个触发器，将数据从一个表移动到另一个表。

我有两个表，一个包含“交易状态”，我想将交易状态更改的记录移至另一个已完成交易的表中。因此，一个表中的值将被删除并插入到另一表中。

请在以下触发器中纠正我：

create trigger transaction_state after update on test_archive for each row begin
insert into test_me(select * from test_archive where new.Transaction_status = 2);
delete from test_archive where new.Transaction_status = 2;
end;

原文

I am new to DB development. Please help me create a trigger for moving data from one table to another.

I have two tables, one contains "Transaction Status" from where I want to move records on transaction status change into another table having completed transactions. So the value in one table will get deleted and will get inserted into another table.

Please correct me in the following trigger:

create trigger transaction_state after update on test_archive for each row begin
insert into test_me(select * from test_archive where new.Transaction_status = 2);
delete from test_archive where new.Transaction_status = 2;
end;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

灵芸 2024-09-25 22:31:53

为什么我感觉我在帮你做作业？正如所写的，当有人将行更新为 Transaction_Status=2 时，您的触发器可能会移动所有行。由于您没有将 NEW 表连接到 test_archive 表，因此您的 WHERE 子句对于所有行都将为 true。

如果您确实希望 Transaction_status=2 的所有行从 test_archive 移至 test_me，则删除 FOR EACH 和对 NEW 表的引用。

create trigger transaction_state after update on test_archive 
  begin 
    insert into test_me
        select * from test_archive where Transaction_status = 2; 
    delete from test_archive where Transaction_status = 2; 
  end;

Why do I feel like I am helping you with homework? Your trigger, as written, will probably move ALL rows when someone updates a row to Transaction_Status=2. Since you didn't join the NEW table to the test_archive table, your WHERE clauses will be true for all rows.

if you really want all rows with Transaction_status=2 moved from test_archive to test_me, then get rid of the FOR EACH and the references to the NEW table.

create trigger transaction_state after update on test_archive 
  begin 
    insert into test_me
        select * from test_archive where Transaction_status = 2; 
    delete from test_archive where Transaction_status = 2; 
  end;

回复收藏 0 原文

~没有更多了~