CODEWITHSUNDEEP
sqlsql-servertriggers
igor.tsutsurupa
igor.tsutsurupa

Reputation: 199

Trigger deleting data periodically

I'd like to have the trigger that removes some rows in given table as given time (e.g. 10 am every day) comes. How do I implement this?

Upvotes: 3

Views: 645

Answers (1)

Marc Guillot
Marc Guillot

Reputation: 6455

Full SQL Server

Instead of a trigger you can set your code in a stored procedure, and define an scheduled job to call that sp every day at 10am.

You create the job :

https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-job

Its command has to be a call to your cleaning sp, something like :

exec (your stored proc name) (and possibly add parameters)

And you schedule it to be called once per day :

https://learn.microsoft.com/en-us/sql/ssms/agent/schedule-a-job

SQL Server Express

Since you have SQL Server Express and can't use the SQL Server Agent, you can instead use the task scheduler of Windows itself to call your cleaning code once per day.

Create an script file cleaning.sql with your delete commands, and create also a batch file cleaning.bat to execute it. Example of the batch file :

sqlcmd -i cleaning.sql

Put those two files on the same folder and set the task scheduler to execute that batch every day at 10am.

Upvotes: 6

Related Questions