Create trigger base on time and insert to another table

Question

How to create a trigger for my case?

1. A table has columns id, value and datetime.
2. 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)

Answer 1

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] < 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

Answer 2

For 2005+ using OUTPUT clause

BEGIN TRY
BEGIN TRAN
Delete
  From Live_Table
OUTPUT d.id,d.value,d.[datetime] into History_Table 
  Where [datetime] < DateAdd(Day, -7, GetDate());
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT @@ERROR
END CATCH