How can I make my database auto update a column when the date in another column reaches the current date?

Question

I have created a asp net core web application that implements an online system of borrowing books from a library. I have created a table with the following structure:

    [Id]                INT      IDENTITY (1, 1) NOT NULL,
    [ApplicationDate]     DATETIME NOT NULL, //the date of borrowing from the app
    [BorrowingDate]      DATETIME NOT NULL, //the date the books are picked up from the library
    [ReturningDate]     DATETIME NOT NULL, //the date of the return
    [NumberOfDaysOfPenalty]  INT      NOT NULL, //number of days past the retuning due date
    [PenaltySum] REAL     NOT NULL, //the sum of money for not returning on time (variable*nrOfDays)
    [UtilizatorId]         INT      NOT NULL,
    [CarteId]           INT      NOT NULL,

Now i want my database to auto update once the current day is past the ReturningDate, and the number of days between these two to be stored into the NumberOfDaysOfPenalty. How can I do that?

Answer 1

Firstly make a stored procedure to check ReturningDate is passed or not. If yes then update NumberOfDaysOfPenalty within the stored procedure.

After that, You can make windows service and create a Job on it, which will fire every day and check if ReturningDate is passed then it will fire your procedure to update the column.