SQL Server Trigger Fire When Courses are Expired

Question

0.00/5 (No votes)

See more:

Dear All,

I have a table called EmployeeCoureses, I want to write a sql trigger on that when Course Expiry Date less then today date, i want to write a trigger which every day is checking course expiry date and send an email notification.

How to do this trigger.

Table Structure

Trn_CourseExpiry

Cs_EmpNo Numeric
Cs_No numeric
Cs_CourseExpiry Date

Thanks
Basit.

Posted 20-Aug-14 20:11pm

basitsar

Add a Solution

Comments

Vinay Mistry 21-Aug-14 2:46am

Its better to create windows service if you can install it otherwise do it manually by creating a page where admin or other users if they have right can do just open page or click button on that page.

Vinay Mistry 21-Aug-14 3:05am

You can use Integration Services Tasks. For more details http://msdn.microsoft.com/en-us/library/ms139892(v=sql.110).aspx

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thomas Nielsen - getCore · Answer 1 · 2014-08-20T20:57:00

Hi
Triggers in MSSQL is a concept for a piece of code which executes when you have changed data in a table. Be that inserts or updates/ deletes, something can happen as a consequence of the operation on data. So you see you do not need a trigger.

What you need in MSSQL is a maintenance plan, if you set up a maintenance plan which executes a TSQL task, which then get all expired cources without expirery date set for instance and update those with today and send a mail.

Maintenance plan
Mail from SQL

Mangesh4 · Answer 2 · 2014-08-21T01:07:00

Solution 3

Hi
Use your query in sql scheduling

Thanks
Mangesh

Posted 21-Aug-14 1:07am

Mangesh4

Magic Wonder · Answer 3 · 2014-08-20T20:19:00

Solution 1

Hi,

As i know, for using Triggers you need some triggering events like insert / update / delete.

Your requirement seems to be different from Trigger concepts. You can create a console application which checks the expiry and send the mail. Also, schedule that application on required time.

Hope this will help you.

Cheers

Posted 20-Aug-14 20:19pm

Magic Wonder

Comments

basitsar 21-Aug-14 2:54am

Other wise in SQL it is possible to create a schedule. on that schedule check the table if there is any expiry date the fire email??

Magic Wonder 21-Aug-14 2:59am

Job Scheduling is possible in SQL, but again for sending mail either you have to Google it how to send mail through SQL or prefer simple way as i stated above.

ChauhanAjay 21-Aug-14 3:17am

You can also create a sql job which check the expiry date and fire the email.

Magic Wonder 21-Aug-14 3:22am

Yeah just checked solution 2.