Dipak Akhade Dipak Akhade - 18 days ago 5
MySQL Question

Use of Scheduler in Web API usung C#

I want use scheduler to achieve following case:

Suppose I have ten user in my database and for all these user I have new table with UserId as foreign key. Lets say UserId,Column1,Column2,CurrentYear are the columns of table. Now as year changes on 31st Dec.(24:00:00) I need to add all existing user again in above table with CurrentYear=newYear and Column1 and Column2 with zero value every year. So that I can consider all refreshed values for every user in new year. Older value will be there just for record maintainance. I am completely new here. How can I achieve this?

Answer

One of possible solutions to your problem is working directly with you database using events.

You create an event that starts on the first day of the next year and reoccurs every year. When it's fired, it reads all records from the table and inserts them back with updated values. Supposing that your table is called "users" it would look like something like this:

CREATE EVENT update_users
ON SCHEDULE EVERY 1 YEAR
STARTS '2017-01-01 00:00:00'
DO 
    INSERT INTO users (UserId, CurrentYear)
    SELECT UserId, YEAR(CURDATE())
    FROM users
    WHERE CurrentYear = YEAR(CURDATE()) - 1
Comments