How to use Triggers in SQL Server 2012 to archive and delete

Question

I will be honest, I know nothing about SQL Server other than what I have tried to pack into my brain in the last two days.... I have found a couple of scripts on your website that sounded like they would work What are ways to move data older than 'Y' days to an archive/history table in MySQL?. This one in particular really seems like it would fit my needs.

But I want to insert the data into a table or database on another partition of the same server and can't figure out how to change the location.

I have SQL Server 2012 Express, running on a Windows Server 2008 R2 service pack 1. We started the database on 11/21/2013 and we hit the 10 GB limit on 12/30/13. We design crowns & bridges, implants and dentures so we have multiple CT scans per patient that get manipulated in 3D imaging and CAD programs multiple times so it creates a lot of data very quickly.

Questions:

1. Should I try to use the triggers built in to my PatientNetDB? [OnAfterDeleteDataSets & OnAfterInsertDatasets]
2. If so how do I change it to make it work like the question from the user I copied above?
3. We may need to pull data back out of this archive, how in the heck do I do that?

I really appreciate any help you can give me, remember I am a total newb to this stuff and unfortunately will need extremely simple step by step or copy and paste directions/scripts.

Thank you so much! Linda Saylor

Answer 1

No, don't use triggers for archiving/deleting. Trigger are fired when specific operations occur - INSERT, UPDATE or DELETE - on certain tables, and you cannot control when and how often triggers are fired. Therefore, triggers should be very small and nimble - you should NOT put large and long-running operations into a trigger. A typical trigger might update a row that's been inserted, or it might put a row into a separate table (an Audit or Command table) - but the trigger itself should never much processing.

What you can and should do is to have scheduled tasks - unfortunately, the SQL Server Agent is not available in the Express edition. With SQL Server Agent you could run certain processing operations (T-SQL scripts) at scheduled intervals, e.g. once every night etc.

Since you're using the Express edition, you'll have to find another way to run a task at given times, possibly by writing a small wrapper in your language of choice (C#, VB.NET, whatever), have that scheduled by the Windows scheduler (Scheduled Tasks in your Windows start menu), which would then kick off / execute a T-SQL script to run the cleanup process and archive your data.