asked on

SQL server trigger to send mail about updated or inserted records

I have written a small sql helpdesk database and I would like a trigger to execute when a user enters a new record or updates a record for that particular record and its values to be emailed to the original submitter. One of the field values already contains the email address of the user. I would like the email formatted with the To box being that email address, the from box being a no-reply email, the subject containing some of the field values, and all the field values in the message body...no attachments or special formatting.

Can onyone help??

This is what I have so far...

--Begin code
--After record updated or inserted-email will be sent to Employee field value
--this field contains email address in the someone@somewhere.com format
IF EXISTS (SELECT name
       FROM sysobjects
       WHERE name = N'Record_Update'
       AND        type = 'TR')
DROP TRIGGER Record_Update
GO

CREATE TRIGGER Record_Update
ON dbo.HelpDeskCalls
FOR UPDATE
AS
DECLARE @sCallNumber varchar(10)
DECLARE @sCallStatus varchar(10)
DECLARE @sEmployee varchar(10)
DECLARE @sPhoneNumber varchar(10)
DECLARE @sLocation varchar(10)
DECLARE @sCategory varchar(10)
DECLARE @sProblem varchar(10)
DECLARE @sITNotes varchar(10)
DECLARE @sAssignedPersonnel varchar(10)
DECLARE @sDateTimeOpened varchar(10)
DECLARE @sDateTimeClosed varchar(10)

SELECT       @sCallNumber = CallNumber,
      @sCallStatus = CallStatus,
      @sEmployee = Employee,
      @sPhoneNumber = PhoneNumber,
      @sLocation = Location,
      @sCategory = Category,
      @sProblem = Problem,
      @sITNotes = ITNotes,
      @sAssignedPersonnel = AssignedPersonnel,
      @sDateTimeOpened = DateTimeOpened,
      @sDateTimeClosed = DateTimeClosed
FROM INSERTED

EXEC master..xp_sendmail @recipients =@sEmployee,
@query = '',
@subject = 'Helpdesk Call# Updated',
@message = 'Helpdesk Call# Updated',
@attach_results = 'TRUE', @width = 250

GO
-- End Code

Thanks for any help
Right now I'm getting the error "Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables." I am fairly sure its due to my variable declarations, but can't seem to find the fix for it.

arbert

That is correct--evidently some of the fields in the "INSERTED" table are text data types and you will probably have to use an instead of trigger:

https://www.experts-exchange.com/questions/20815377/Use-'text'-data-types-in-triggers.html

The from address is going to be the from address of the SQLAgent/SQLMail account (unless you use CDO or SMTPmail) and can't be changed.

Brett

arbert

From Books Online:

In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the
original update table. When the compatibility level is 65 or lower, null
values are returned for inserted or deleted text, ntext, or image columns
that allow null values; zero-length strings are returned if the columns are
not nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.

Melih SARICA

post
table structure of dbo.HelpDeskCalls pls

jasonabrown

ASKER

Column Name            Data Type      Length            AllowNulls
CallNumber            int            4            no
Employee            nvarchar      50            no
PhoneNumber            nvarchar      50            no
Location            nvarchar      50            no
Category            nvarchar      50            no
Problem                  nvarchar      50            no
ITNotes                  nvarchar      50            yes
AssignedPersonnel      ntext            16            yes
DateTimeOpened            datetime      8            no
DateTimeClosed            datetime      8            yes
TotalTimeOpen            decimal            13            no

TotalTimeOpened is a formula: (round((convert(decimal,datediff(minute,[DateTimeOpened],[DateTimeClosed])) / 60),2))

jasonabrown

ASKER

Just in case this is important to. The database is accessed and updated entirely through .asp web pages. I have a script that upon call submission the values can be emailed out unfortunately I have not been able to find out where to put the script or access the variables in the update pages. This project started out using an access db and the frontpage database interface wizard. I converted the db to sql and altered much of the code on the web pages for it to work. I would rather do all notification from the server but If anyone know how to do this from the update.asp pages which are created automatically by the db wizard in frontpage that would work too.

      'SENDMAIL SCRIPT
      Dim objSendMail
      'Create CDO object for sending mail via smtp
      Set objSendMail = CreateObject("CDO.Message")
      objSendMail.From = "ITWEB"
objSendMail.To = arFormValues0(1)
objSendMail.Subject = "New Open Call Submitted to ITWEB"
objSendMail.TextBody = "Please do not reply to this email" & Chr(13) & "Call Status = " & arFormValues0(3) & ""&Chr(13)&"" & "Employee = " & arFormValues0(1) & ""&Chr(13)&"" & "Phone Number = " & arFormValues0(2) & ""&Chr(13)&"" & "Location = " & arFormValues0(4) & ""&Chr(13)&"" & "Category = " & arFormValues0(5) & ""&Chr(13)&"" & "Problem = " & arFormValues0(0) & ""&Chr(13)&"" & "Date/Time Opened = " & Now
           objSendMail.Configuration.Fields.Item _
                 ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            objSendMail.Configuration.Fields.Item _
                  ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mailserver.work.com"
            objSendMail.Configuration.Fields.Item _
                  ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            objSendMail.Configuration.Fields.Update
            objSendMail.Send
      Set objSendMail = Nothing
      'END SENDMAIL SCRIPT

The above script is in my submission page just before the records are submitted to the db. The update pages only have an SQL update query and 50 other lines of code.
update code below

<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>HelpDeskCalls -- Updated Record</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body stylesrc="../../../itindex.html" link="#000080" vlink="#000080" alink="#000080">
 <p>

</p>


<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="UPDATE HelpDeskCalls SET CallStatus = '::CallStatus::' , Employee = '::Employee::' , PhoneNumber = '::PhoneNumber::' , Location = '::Location::' , Category = '::Category::' , Problem = '::Problem::' , ITNotes = '::ITNotes::' , AssignedPersonnel = '::AssignedPersonnel::' , DateTimeOpened = '::DateTimeOpened::' WHERE (CallNumber = ::CallNumber::)"
fp_sDefault="CallStatus=&Employee=&PhoneNumber=&Location=&Category=&Problem=&ITNotes=&AssignedPersonnel=&DateTimeOpened=&CallNumber="
fp_sNoRecords="Record updated in table."
fp_sDataConn="HELPDESK"
fp_iMaxRecords=1
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&CallNumber=3&CallStatus=202&Employee=202&PhoneNumber=202&Location=202&Category=202&Problem=203&ITNotes=203&AssignedPersonnel=202&DateTimeOpened=135&DateTimeClosed=135&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>



<p></p>


<p><a href="../../../itindex.html">ITWEB HOME</a></p>

</body>

</html>

arbert

So no comment on any of my posts??

jasonabrown

ASKER

I have tried using INSTEAD OF and not changing any other code and get the same errors. I am fairly new to sql and don't know all the syntax and commands. I have read about the INSTEAD OF trigger, but it seems that it is used to see what would happen to the table or record but it would not actually alter the table. I need the table to be altered, and then an email sent tothe employee field so that the cutomers know when the helpdesk team has changed information on a helpdesk call.

jasonabrown

ASKER

This has been resolved using an AFTER UPDATE trigger on the inserted tables and exec using xp_smtp_sendmail.

ASKER CERTIFIED SOLUTION

jasonabrown

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arbert

"close this question."

You're responsible for doing that....