Click here to Skip to main content
12,077,586 members (47,067 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
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 21:11pm
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Hi

Use your query in sql scheduling


Thanks
Mangesh
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160212.1 | Last Updated 21 Aug 2014
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100