CODEWITHSUNDEEP
sql-server
mmcc
mmcc

Reputation: 37

SQL Server trigger to update data automatically

I'm designing a library management database schema, let's say there is a table "Borrow"

Borrow

id
user_id
book_id
borrow_date
due_date
isExpired
expired_day (number of days after the book is expired)
fine

Can the SQL Trigger implement the following circumstances?
1.Compare the due_date with Today, if it's same-->send email-->mark isExpired to true
2.If isExpired is marked to true-->compare the difference between today and due_date, and
update expired_day--->update fine (expired_days * 5)

Upvotes: 0

Views: 4095

Answers (2)

NG.
NG.

Reputation: 6053

Since you want to check all the records of the library daily and want them to be updated accordingly, it is better to make a daily job and schedule an agent and set a particular time so that this daily job would be executed everyday automatically.
pls Note : You should keep in mind to choose that time when you feel your application would be least used during the entire day.

Creation of Agent : http://msdn.microsoft.com/en-us/library/ms181153(v=sql.105).aspx

Upvotes: 0

Mike Parkhill
Mike Parkhill

Reputation: 5551

A trigger only fires when something happens on the table or row. It won't fire continuously (or daily). If nothing happens to the table then your trigger will never fire so your checks can't be done.

So, the trigger you describe would work when you first insert a record into the row, but there's no automatic way with a trigger for it to fire after the due date period to check for the expiry and fine.

You would most likely need to setup a stored procedure that contained your code and find a way to run that on scheduled basis.

The following link goes over how to set that up: Scheduled run of stored procedure on SQL server

Upvotes: 2

Related Questions