sql - MySQL trigger. How to store each row of data in table for a limited time? -

sql - MySQL trigger. How to store each row of data in table for a limited time? -

there table in database every row of should stored limited time (1 minute). i'm trying on insert trigger this:

create trigger `temp_data_trig` after insert on `tokens` each row begin sleep(60); delete `tokens` id = new.id; end

but when i'm trying insert new row i'm getting this:

can't update table 'tokens' in stored function/trigger because used statement invoked stored function/trigger.

what wrong trigger , right way store each row of info limited time?

in general, don't want sleep in trigger. locks table.

i suggest take 1 of next approaches.

the first include date/time stamp when inserted. have job goes through deletes records more min old. note puts fair amount of load on database. alternatively, maintain records, inserting them @ will, , have view selects ones created in lastly minute:

create view v_table select * table createdat >= now() - interval 1 minute;

the final solution partition info min intervals. can drop partitions minimal impact on performance of database ("minimal" relative other options might lock table longer periods of time).

mysql sql triggers sql-insert sql-delete