sql - MySQL trigger to delete old record and insert new one -

i have table below

table name: sda_user_eform_data  ack_no   name       description 1        name1     name1 2        name2     name2 3        name3     name3 

i have table sda_user_eform_data_bckup has same structure sda_user_eform_data. want store 5 rows(latest rows) in sda_user_eform_data , whenever ackno greater 5 old values should moved second(sda_user_eform_data_bckup) table.

for first have copied rows sda_user_eform_data table sda_user_eform_data_bckup table. have created following trigger have checked ack_no , if greater 5 deleted oldest ack_no , insert new value bckup table.

delimiter $$ create   trigger 'copy_eform_data' after insert   on asdb.sda_user_eform_data   each row begin   if (select count(s.ack_no) asdb.sda_user_eform_data s)>5     delete asdb.sda_user_eform_data old.ack_no=(select min(s.ack_no) asdb.sda_user_eform_data s);   insert asdb.sda_user_eform_data_bckup select * asdb.sda_user_eform_data ack_no=select max(s.ack_no) asdb.sda_user_eform_data s; end$$ delimiter ; 

i not able find out trigger went wrong not executing. suggestion highly welcoming.

thanks in advance.

that's because trigger doesn't exist. problem here

create   trigger 'copy_eform_data' 

with single quotes copy_eform_data string.

have @ post: when use single quotes, double quotes, , backticks?

also should read new , old keywords in triggers. trigger never matches line.

and here

where ack_no=select max(s.ack_no) asdb.sda_user_eform_data s 

you're missing parantheses.

apart that, didn't have deep thought logic honest, because don't see point in whole question. why want have duplicate data? guess out of performance reasons? have table indexed appropriately , there should no problem. , 5 latest entries of table use

from yourtable order when_was_the_entry_created_or_something desc  limit 5 

you can have columns like

created timestamp default current_timestamp on update current_timestamp 

to use in order by. , want index on column.