doubt with date in sql tables

Asked

Viewed 56 times

3

good, 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 was to have a trigger in this table that would check the date and when this date was due change this type to another value and change the date to a new day too, for example, when this date was due then it would be like this:

usernum  /  type  /  expiredate
   1         0      2019-03-20 13:04:42.710
sql sql-server trigger

  • Sorry to ask but why don’t you use the now() in the query itself ?

  • I’m sorry but I’m layman still, how do I use this now()

  • For example, when you change the type, you should run a query like this update tabela set type = 0 where id usernum instead you could use update tabela set type = 0, expiredate = now() where id usernum with this the date will catch the exact time of the update

  • @Bulfaitelo doesn’t really want a trigger. He wants you to change automatically when you win

  • @Sorack Truth I understood the opposite.

  • I saw a staff commenting on another similar question to mount a job that check every n minutes, but do not know how to do

  • @Merlin Normally this kind of thing I would do in my application, for example using crontab or windows task scheduler

  • 1

    @Merlin soon after lunch I write a reply

  • @Thank you so much for your attention

  • you also @Bulfaitelo

  • Related: Dynamic insertion according to date and days of the week

  • 1

    You use sql or mysql server?

  • @Leticiarosa sql server 2008

  • @Merlin you can’t just create a view and bring that value filled in according to the date?

  • 1

    @Merlin just posted an answer to sql server

Show 10 more comments

1 answer

1

Trigger only works when you perform an action in the database (Insert, update, delete...). I believe that the best solution in your case would be to schedule the task.

In SQL Server you can use SQL Server Agent for scheduling. This service is not available in SQL EXPRESS versions. If this is your version you can use the 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:

  1. Click Start, click Programs, then click SQL Server 2008.
    1. Click Configuration Tools, then click SQL Server Configuration Manager.
    2. Expand SQL Server 2008 services.
    3. Locate the SQL Server Agent service. The SQL Server Agent service is called "SQL Server Agent" for standard instances and "SQL Server Agent (instance name)" for named instances.
    4. Click SQL Server Agent and then click Properties.
    5. On the Logon tab, click to select this account check box. Specify a different account name and password.
    6. In the status section of the service, click Start, then click Ok.

After the service started you need to create Job. To do this go to Management Studio and, in Object Explorer (side tab where you have the database and the tables) you will have the option SQL Server Agent and there you can create a new job.

Situation 2 - Express version of SQL SERVER

If you use the Express version of SQL SERVER SQL Server Agent will not start. To run your script then you can use the task scheduler to do this service for you "manually"

To do this create a file . bat 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 the Windows task scheduler that runs this bat every day at a specific time.

A command you can use to accomplish this task of adding more time on the date, for your example, is the DATEADD() adding an integer value to a part of the date of its choice.

Browser other questions tagged sql sql-server trigger

You are not signed in. Login or sign up in order to post.