March 5, 2009 at 5:43 am
hi guys,
Very new at all this so please bear with me 🙂
Our sql database is a call management system - new calls are booked, engineers are then scheduled etc... What i am trying to do is create an email trigger so that whenever a new "Call" is created and email gets sent to a specific email address based on the customer that the call was logged to.
There are 2 tables that will need to be used:
SCCall, this contains all of the information required in the email:
Call_Num, Call_Calt_Code, Call_Problem, Call_Site_Num, Call_Site_Post_Code, Call_InDate,
this table also contains the field Call_Cust_Num.
This Call_Cust_Num links to another table called SCCust where the email address for the email to be sent to is contained in the Cust_Email field.
If anyone can help i would be extremely grateful!
Sam
Extreme Newbie
EDIT
After reading some other posts here it seems triggers may not be the best option. Any other ideas of how to acheive this?
Sam Marsden
March 5, 2009 at 5:53 am
Please read carefully in the article in my sig and post the structure of you table and some sample data if you want to find the correct solution!
March 5, 2009 at 5:59 am
Hi Dugi,
Sorry if i haven't posted the cotrrect info however i am very new at this and im not quite sure i can do what you're saying.
i didn't create the database, it was created by an external source.
Sam Marsden
March 5, 2009 at 6:18 am
sam.marsden (3/5/2009)
i am very new at this and im not quite sure i can do what you're saying.i didn't create the database, it was created by an external source.
Sure you can. 🙂 Doesn't matter who created the database.
What we're asking for is the table structure (as a create table statement) so we can easily create the tables to test any solution.
Some sample data (in the form of insert statements) to test on
Explain desired results, ie mail to who, when, with what in it, etc.
How are you sending the mail? SQLMail? DBMail? Some other app?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 7:01 am
If you are using DBMail (sp_send_dbmail) you are probably all right sending an email using a trigger. This is because DBMail is now an asynchronous service using Service Broker, so calling sp_send_dbmail actually puts the email in a queue and another process then sends it. This means that sending email now happens outside the scope of the initial transaction.
It also depends on how you are determining the recipients of the email. If you are looping through a table then you probably want to do it outside the initial transaction (keep the transaction as short as possible).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2009 at 7:13 am
ok here goes nothing.....
CREAT TABLE SCCall
(
Call_Num(PK, int, not null)
Call_Calt_Code(varchar(3), null)
Call_Problem(varchar(50), null)
Call_Site_Num(varchar(12), null)
Call_Site_Post_Code(varchar(10),null)
Call_InDate(datetime, null)
Call_Cust_Num(varchar(12),null)
)
CREATE TABLE SCCust
(
Cust_Num(PK,varchar(12),not null)
Cust_Email(varchar(100),null)
)
INSERT INTO SCCall
(Call_Num, Call_Calt_Code, Call_Problem, Call_Site_Num, Call_Site_Post_Code, Call_InDate, Call_Cust_Num)
SELECT '123456','S24','Replace Key','CON987654','TN12 3QS', '01/03/2009', 'CONSIGNIA' UNION ALL
SELECT '123457','S04','Faulty Safe','STA55236','SE14 3DA', '02/03/2009', 'STARBUCK' UNION ALL
SELECT '123458','S03','Reprogram','PHU5554','TW19 6JZ', 03/03/2009, 'PIZZAHUT'
INSERT INTO SCCust
(Cust_Num, Cust_Email)
SELECT 'CONSIGNI', '1@1.com' UNION ALL
SELECT 'STARBUCK', '2@2.com' UNION ALL
SELECT 'PIZZAHUT', '3@3.com'
Not sure if it's right, still trying to tech myself all this! lol
In my DB there is a link between sccall and sccust on the cust_num field, what i would like is whenever a call is logged all of the details get emailed to the email address located the the cust_email field. One thing that has just popped into my head is that we will have some cases where there is no email, is it possible that we can tell the trigger that if there is no email then dont try and send one rather than causing an error?
Please note edit - changed problem to be varchar(50)
Sam Marsden
March 5, 2009 at 7:28 am
Great. One last thing. How is the email going to be sent? Are you suing DBMail? Do you have a mailing program? Or is there nothing set up at the moment to do the mailing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 7:31 am
nothing set up at the mo i don't think... let me just jump onto the server and i'll find out
Sam Marsden
March 5, 2009 at 7:34 am
hi gail, nope nothing set up - any suggestions as to what is the best way?
Sam Marsden
March 5, 2009 at 7:38 am
DBMail's probably the easiest. I think Jack knows more about it than I do, so I'll defer to him.
Are you the DBA? If not, can you ask the DBA if DBMail is permitted?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 7:43 am
well..... at the moment we kind of don't have an official one lol, i've asked the question but it may take a while for someone to come back to me, is there a way that i can check?
Sam Marsden
March 5, 2009 at 8:07 am
sam.marsden (3/5/2009)
is there a way that i can check?
I'm asking if it's permitted by company/department policies. That's not something that a script can check.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 8:12 am
oh ok lol i misunderstood - there's no problems with it at all.
Sam Marsden
March 5, 2009 at 8:19 am
You can see if database mail is enabled by running this:
sys.sp_configure @configname = 'Database Mail XPs'
This will return something like this:
name minimum maximum config_value run_value
-------------------------- -------- --------- ------------ ----------
Database Mail XPs 0 1 1 1
The key values are config_value and run_value. Both need to be 1 in order to use Database Mail.
If the config_value is 1 and the run_value is 0 you need to run the RECONFIGURE command to "activate" database mail.
To setup Database Mail your best resource is Books On Line under the Database Mail topic. Here is a link[/url] to an article on SSC about setting up Database Mail as well.
Once you have database mail setup and tested, the trigger or other code to use it is pretty straightforward.
I would recommend doing it outside of a trigger though. Mainly because it will be difficult to code the trigger to handle a set based insert without some kind of loop which will prolong the transaction.
I would recommend a scheduled job that either reads the calls entered in the last N minutes and sends out emails, or using the trigger to either use Service Broker (and I have not used Service Broker) to process the messages or inserts a row into a mail_queue table you would create and then a job processes (this is how I would have handled it pre-2005).
What data would want to include in the email?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2009 at 8:33 am
thanks Jack ill go and check it in a sec, the info that we would like to be in the email is as follows:
Subject: Insafe Helpdesk - New Call Logged [Call_Num]
Body:
[Call_Num]
[Call_Site_Post_Code]
Response requested: [Call_Calt_Code]
[Call_Problem]
~~ Here we would have the generic "this email account is not monitored etc etc etc ~~
Other fields may be included but as a test these will suffice
Sam Marsden
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply