Solved

SQL server trigger to send mail about updated or inserted records

Posted on 2004-04-28
11
1,305 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:jasonabrown
  • 5
  • 4
11 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10945502
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:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20815377.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
0
 
LVL 34

Expert Comment

by:arbert
ID: 10945643
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.
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 10946945
post
  table structure of dbo.HelpDeskCalls pls

0
 

Author Comment

by:jasonabrown
ID: 10948541
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))
0
 

Author Comment

by:jasonabrown
ID: 10948697
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">
&nbsp;<p>
<!--webbot bot="PurpleText" PREVIEW="-Important-  If you modify this Database Results region using the Database Results Wizard, then your Database Editor will no longer work.  If you accidentally open the Database Results Wizard, simply click Cancel to exit without regenerating the Database Results region." -->
</p>

<!--webbot bot="DatabaseRegionStart" s-columnnames s-columntypes s-dataconnection="HELPDESK" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="FALSE" b-listseparator="FALSE" i-ListFormat="0" b-makeform="FALSE" s-RecordSource s-displaycolumns s-criteria s-order s-sql="UPDATE HelpDeskCalls SET CallStatus = '::CallStatus::' , Employee = '::Employee::' , PhoneNumber = '::PhoneNumber::' , Location = '::Location::' , Category = '::Category::' , Problem = '::Problem::' , ITNotes = '::ITNotes::' , AssignedPersonnel = '::AssignedPersonnel::' , DateTimeOpened = '::DateTimeOpened::'  WHERE (CallNumber = ::CallNumber::)" b-procedure="FALSE" clientside SuggestedExt="asp" s-DefaultFields="CallStatus=&amp;amp;Employee=&amp;amp;PhoneNumber=&amp;amp;Location=&amp;amp;Category=&amp;amp;Problem=&amp;amp;ITNotes=&amp;amp;AssignedPersonnel=&amp;amp;DateTimeOpened=&amp;amp;CallNumber=" s-NoRecordsFound="Record updated in table." i-MaxRecords="1" i-GroupSize="0" u-dblib="../../../_fpclass/fpdblib.inc" u-dbrgn1="../../../_fpclass/fpdbrgn1.inc" u-dbrgn2="../../../_fpclass/fpdbrgn2.inc" Tag="BODY" startspan BOTID="0" preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" b-WasTableFormat="FALSE" --><!--#include file="../../../_fpclass/fpdblib.inc"-->
<% 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
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan -->

<p><!--webbot bot="PurpleText" PREVIEW="This is the UPDATE query." --></p>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="FALSE" u-dbrgn2="../../../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside Tag="BODY" startspan preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" --><!--#include file="../../../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan -->
<p><a href="../../../itindex.html">ITWEB HOME</a></p>

</body>

</html>
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:arbert
ID: 10948931
So no comment on any of my posts??
0
 

Author Comment

by:jasonabrown
ID: 10949025
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.
0
 

Author Comment

by:jasonabrown
ID: 10980316
This has been resolved using an AFTER UPDATE trigger on the inserted tables and exec using xp_smtp_sendmail.
0
 

Accepted Solution

by:
jasonabrown earned 0 total points
ID: 10981058
Please close this question.

Problem was with records being inserted from the web page that had null values. Web pages were modified to not allow blank forms and 2 seperate triggers were created. One for Insert where an
end user enters a ticket from the web, only accessing a few of the table values, and the other Trigger for Update only, where IT personnel enters changes to tickets and Call Close Time (the only value which can be null) is set to a string value if the call status is still open...


--Begin code
--After record updated 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 HelpDeskCalls
AFTER UPDATE
AS

DECLARE @sCallStatus varchar(10), @sEmployee varchar(100), @sPhoneNumber varchar(50), @sLocation varchar(50), @sCategory varchar(50), @sProblem nvarchar(1000), @sITNotes nvarchar(1000), @sAssignedPersonnel varchar(100), @sDateTimeOpened varchar(30), @sDateTimeClosed varchar(30)
DECLARE @Message nvarchar(4000)
DECLARE @Message_Notes nvarchar(4000)
SET @sCallStatus = (SELECT CallStatus FROM INSERTED)
SET @sEmployee = (SELECT Employee FROM INSERTED)
SET @sPhoneNumber = (SELECT PhoneNumber FROM INSERTED)
SET @sLocation = (SELECT Location FROM INSERTED)
SET @sCategory = (SELECT Category FROM INSERTED)
SET @sProblem = (SELECT Problem FROM INSERTED)
SET @sITNotes = (SELECT ITNotes FROM INSERTED)
SET @sAssignedPersonnel = (SELECT AssignedPersonnel FROM INSERTED)
SET @sDateTimeOpened = (SELECT CONVERT(varchar(30),DateTimeOpened) FROM INSERTED)
SET @sDateTimeClosed = (SELECT CONVERT(varchar(30),DateTimeClosed) FROM INSERTED)
IF @sCallStatus = 'Open' SET @sDateTimeClosed = 'Call not yet closed'

SET @Message_Notes = 'Call Status = ' + @sCallStatus + CHAR(13) + CHAR(13) + 'Employee = ' +
@sEmployee + CHAR(13) + CHAR(13) + 'Phone Number = ' + @sPhoneNumber + CHAR(13) + CHAR(13) +
'Location = ' + @sLocation + CHAR(13) + CHAR(13) + 'Category = ' + @sCategory + CHAR(13) + CHAR(13) +
'Problem = ' + @sProblem + CHAR(13) + CHAR(13) + 'ITNotes = ' + @sITNotes + CHAR(13) + CHAR(13) +
'AssignedPersonnel = ' + @sAssignedPersonnel + CHAR(13) + CHAR(13) + 'Date & Time Opened = ' +
@sDateTimeOpened + CHAR(13) + CHAR(13) + 'Date & Time Closed = ' + @sDateTimeClosed

declare @rc varchar(8000)
exec @rc = master.dbo.xp_smtp_sendmail
    @FROM       = N'ITWEB',
    @TO         = @sEmployee,
    @CC         = @sAssignedPersonnel,
    @Priority   = N'HIGH',
    @Subject    = 'Hepldesk Call Updated',
    @Type       = N'text/plain',
    @Message    = @Message_Notes,
    @Codepage   = 0,
    @Timeout    = 10000,
    @Server     = "mailrelay.mywork.com"
select RC = @rc

go
0
 
LVL 34

Expert Comment

by:arbert
ID: 10983257
"close this question."

You're responsible for doing that....
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now