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...
--After record updated or inserted-email will be sent to Employee field value
--this field contains email address in the email@example.com format
IF EXISTS (SELECT name
WHERE name = N'Record_Update'
AND type = 'TR')
DROP TRIGGER Record_Update
CREATE TRIGGER Record_Update
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
EXEC master..xp_sendmail @recipients =@sEmployee,
@query = '',
@subject = 'Helpdesk Call# Updated',
@message = 'Helpdesk Call# Updated',
@attach_results = 'TRUE', @width = 250
-- 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.