Move Data from one table to another using on update trigger

Question

Hi All,

I New in DB development. Please help me create trigger for moving data from one table to another. I have two tables one is containing "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 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;

Thanks to you all in advance.

Answer 1

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;