0

Is there a way to set up SQL Server to automatically delete some rows based on certain conditions?

For example I have a table TblNote with a column createDate to store date that row was created, and a column deleteDate to store date so that this row will be deleted when deleteDate matches current date.

How can I set up server to do that?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

2 Answers2

3

You could probably use SQL jobs that will run on daily basis at a certain time, pick those records which have delete-date less than or equal to current date and will perform delete operation on those records. You can see this link to learn how to schedule sql jobs.

Community
  • 1
  • 1
Shilpa Soni
  • 2,034
  • 4
  • 27
  • 38
2

Yes there is :

Add a trigger for column insert or update. However this will work only if a record DMQ takes place.

or

create a procedure that checks it and place it in a job monitor(if you have licensed SQL server) or Task scheduler.

Codeek
  • 1,624
  • 1
  • 11
  • 20
  • The trigger is not going to fire at the point in time you want to delete the row - that doesn't sound like a solution .... – marc_s Sep 18 '15 at 05:40
  • yep. That is why I mentioned that it will work only in case of Data modification Query runs and not always. One could always manipulate the condition to delete all records with deleteDate less than equal to current date. So that whenever the trigger fires, it will take care of all the records to be deleted. I agree it will not take place in real time. – Codeek Sep 18 '15 at 06:29
  • Plus I would really not like the idea of kicking off a long-running delete operation when I update an arbitrary row of data in my table. Scheduling a SQL Server job is the much better alternative - once at 1am in the night, check the tables for row that have "expired" and then delete those, while no one is working and no one is blocked by a delete – marc_s Sep 18 '15 at 06:31