Click here to Skip to main content
Rate this: bad
good
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 at 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 at 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 at 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 at 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 at 21-Aug-14 3:17am
   
You can also create a sql job which check the expiry date and fire the email.
Magic Wonder at 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
0 OriginalGriff 304
1 Maciej Los 285
2 Sergey Alexandrovich Kryukov 255
3 Shweta N Mishra 216
4 PIEBALDconsult 174
0 OriginalGriff 7,660
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,760


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 21 Aug 2014
Copyright © CodeProject, 1999-2014
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