CODEWITHSUNDEEP
sqlsql-serversql-delete
bashbin
bashbin

Reputation: 414

Delete a record after a period of time automatically in SQL Server?

I have a table which has two attributes: ID & Datetime when that record was created.

How can I make a trigger (procedure?) to delete a record after, say, 1 day?

I want a task which executes itself every X time instead of manually having to do it.

Upvotes: 5

Views: 12172

Answers (3)

Mohammad Reza Shahrestani
Mohammad Reza Shahrestani

Reputation: 1149

I suggest you to use SQL Server agent and write a stored procedure that deletes every rows that date is passed one day.

You can find how to use Sql server agent jobs here in this link.

And the stored procedure like :

CREATE  PROCEDURE DeleteRows()
AS
BEGIN
DELETE FROM mytable
WHERE (DATEDIFF(DAY,  DATEADD(day, -1, DateColum), GETDATE())) >= 1
END

Edit : The number 1 in where statement is days. you can change it to what you want to use.

Upvotes: 4

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

You could create a SQL Job to run after one day (or daily as you want.) and execute a specified stored procedure that carries a simple delete statement.

Follow the next topic:-

http://technet.microsoft.com/en-us/library/ms190268.aspx

Upvotes: 0

mayersdesign
mayersdesign

Reputation: 5310

The statement to delete a record older than one day is:

DELETE FROM tableName WHERE DATEDIFF(day, getdate(), dateColumn) < -1

You would need to cron that statement using whatever language you have available, php for instance.

That said of course it's hard to imagine a scenario where you would want to be deleting records in the first place ;)

Upvotes: 0

Related Questions