Trigger deleting data periodically

Question

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?

Answer 1

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.