Create trigger base on time and insert to another table How to create a trigger for my case?


A table has columns id, value and datetime.
I want to create a trigger; when data is more than 1 week old move it to another table (historical); data less than 1 week old will be remain in the current table (live table)
It could go something like this. Grab the old data into a temp table. Insert it into the history table then delete from the live table. I'll leave the error handling as an exercise for the reader.

Begin Transaction archive

Select id, value, [datetime]
  Into #historical_to_archive
  From Live_Table
  Where [datetime] &lt; DateAdd(Day, -7, GetDate());

Insert Into History_Table (id, value, [datetime])
Select id, value, [datetime]
  From #historical_to_archive;

Delete
  From Live_Table
  Where id In (Select id From #historical_to_archive);

Drop Table #historical_to_archive;

Commit Transaction archive