Question:
well, I have a table with the following information
usernum / type / expiredate
1 1 2019-03-03 13:04:42.710
what I wanted to do is have a trigger in this table that checks the date and when that date expires, change this type to another value and change the date to a new day too, for example, when this date expires, it would look like this:
usernum / type / expiredate
1 0 2019-03-20 13:04:42.710
Answer:
The trigger only works when you perform an action on the database (insert, update, delete…). I believe that the best solution in your case would be a task scheduling.
In SQL Server you can use SQL Server Agent to make appointments. This service is not available in SQL EXPRESS versions. If this is your version you can use Windows Task Scheduler to schedule a bat that runs your SQL script.
Situation 1 – SQL Server Agent You need to activate the service first. How to do this:
- Click Start, click Programs, then click SQL Server 2008.
- Click Configuration Tools and then click SQL Server Configuration Manager.
- Expand SQL Server 2008 services.
- Locate the SQL Server Agent service. The SQL Server Agent service is named "SQL Server Agent" for default instances and "SQL Server Agent (instance name)" for named instances.
- Click SQL Server Agent and then click Properties.
- On the Logon tab, click to select the This account check box. Specify a different account name and password.
- In the Service Status section, click Start and then click OK.
After the service started you need to create the Job. For that, go to Management Studio and, in Object Explorer (side tab where you have the database and tables) you will have the option SQL Server Agent and there you will be able to create a new job.
Situation 2 – SQL SERVER Express Version
If you use the Express version of SQL SERVER the SQL Server Agent will not start. To run your script then you can use the task scheduler to do this job for you "by hand"
To do this, create a .bat file with the following command:
sqlcmd -i script.sql
In the same folder save the script.sql file with the SQL code you want to run. Then create a task in Windows task scheduler that runs this bat every day at a specific time.
One command you can use to accomplish this task of adding more time to the date, for your example, is DATEDD() which adds an integer value to a part of the date of your choice.