2

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)
3
  • 2
    I think maybe a trigger is not the right technique to achieve this. Another way that might make more sense is to create an SQL script which moves the data then make a SQL Agent job which runs on a schedule (weekly, daily, hourly, ...) and executes your script.
    – mendosi
    Nov 25, 2016 at 4:20
  • can you give an example of this script?
    – wmasmaddy
    Nov 25, 2016 at 4:27
  • Yeah, trigger is the wrong technology for this. A scheduled job is the way to go. Nov 25, 2016 at 4:29

2 Answers 2

2

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.