Move data from one table to another using on update trigger

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 c开发者_如何转开发ontains "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;

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;