0

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)

mmcc
  • 37
  • 2
  • 8

2 Answers2

2

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

Community
  • 1
  • 1
Mike Parkhill
  • 5,511
  • 1
  • 28
  • 38
  • I want to ask one more question, i write a program that can insert data into SQL server database, should the machine(not the one in program development) install SQL server fisrt? – mmcc Oct 09 '12 at 02:41
0

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

NG.
  • 5,695
  • 2
  • 19
  • 30
  • I want to ask one more question, i write a program that can insert data into SQL server database, should the machine(not the one in program development) install SQL server fisrt? – mmcc Oct 09 '12 at 02:42
  • I could not understand your question clearly, but only the server machine(where you deploy your code) needs to have an installed sql server. – NG. Oct 09 '12 at 02:49