February 21, 2008 at 10:42 pm
Hi All,
PM from sales dept told me that he wasn't able to find the leads that he made yesterday.I figured out that the sales application is deleting the leads based on some business logic.I told the same to PM.He said that he want to be informed whenever a lead is deleted and also want to have the lead details.
I created a trigger on the table but wasn't able to schedule the job based on the particular event.
Kindly provide me the solution, if any ...other wise i will ask my software people to send the mail through the sales application.
Regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 22, 2008 at 9:04 am
a delete trigger linked to dbmail or smtp mail ( search the site for procs which send smtp mail ) should do the trick - or write the deleted info into another table.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 22, 2008 at 9:58 am
Agreed - dump the stuff to a table, and notify them somehow that the stuff was deleted. Preferrably don't put the e-mailing in the trigger, since you don't want your ability to delete being tied to whether your SMTP server is having a bad day. I would schedule the e-mail process outside of the trigger (like - say - every 15 minutes).
NET SEND would be an unreliable solution, since you wouldn't get anything you aren't logged in at the time.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 12:41 pm
Matt Miller (2/22/2008)
. I would schedule the e-mail process outside of the trigger (like - say - every 15 minutes).NET SEND would be an unreliable solution, since you wouldn't get anything you aren't logged in at the time.
Hi ,
Thanks for the support...however..
What I want is that the mail is sent as soon as a record is deleted from the table.I dont want to schedule the email process.:)
Regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 22, 2008 at 1:51 pm
The only issue with putting the sending an e-mail through a trigger is that any failure during the trigger execution will make the trigger AND the action roll back. So - if your e-mail server is down for maintenance, then the delete will be CANCELLED (rolled back).
Just something to be aware of.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 3:19 pm
I am assuming you could have this in seconds, if so you could configure a SQL job to run and have that run N # of seconds to pick up the deletions and send an email.
You can use the @freq_subday_type to the undocumented 0x2, which accounts for seconds and the @freq_subday_interval = N, runs every N of seconds
February 24, 2008 at 9:45 pm
Hi,
Do I have any other option apart from trigger.
Regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 24, 2008 at 9:48 pm
andrewkane17 (2/22/2008)
I am assuming you could have this in seconds, if so you could configure a SQL job to run and have that run N # of seconds to pick up the deletions and send an email.You can use the @freq_subday_type to the undocumented 0x2, which accounts for seconds and the @freq_subday_interval = N, runs every N of seconds
Hi,
Is that mean that I will be able to schedule the job as soon as a row is deleted .
Regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 25, 2008 at 11:15 pm
Database mail is your answer on SQL 2005, take a look at "Database Mail" and sp_send_dbmail in BOL.
Database mail maintains it's own mail queues, etc. and SQL Server itself should not be affected by an unavailable SMTP server.
February 25, 2008 at 11:23 pm
Aye, on SQL Server 2005, database mail is the answer, as Joe has indicated. If you're on SQL Server 2000, or you are using SQL Mail on 2005 (you shouldn't be, it's deprecated for a reason), you'll want to do the store in a table and schedule. Otherwise, a single hiccup and you get the rollback, as already described.
On a related note, net send should generally not be used. There is the delivery problem that's mentioned, however, from a security perspective, it's also considered a best practice to disable the Messenger service, both on clients and on servers. If the Messenger service isn't running on either location, the message won't be delivered.
K. Brian Kelley
@kbriankelley
February 26, 2008 at 10:18 pm
Thanks all ......
I've already started studying Database mail:)
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 27, 2008 at 9:29 am
You can setup database mail using the link given below.
http://www.sql-articles.com/index.php?page=articles/database_mail.html
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
April 8, 2008 at 4:41 pm
you can actually execute a non-sch job from the trigger
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply