Click here to Skip to main content
Rate this: bad
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
Cs_EmpNo Numeric
Cs_No numeric
Cs_CourseExpiry Date

Posted 20-Aug-14 21:11pm
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
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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

Solution 3

Use your query in sql scheduling

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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.

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 400
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 DamithSL 95
4 Garth J Lancaster 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220

Advertise | Privacy | Mobile
Web02 | 2.8.141220.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