Link to home
Start Free TrialLog in
Avatar of Sanjay
SanjayFlag for United States of America

asked on

SQL trigger to send an email to a person after an update

Need help to create a sql trigger code to send an email  with subject heading "Updated SCAR <number> (whatever this new number is after insert) generated"    and message of the body is        "The SCAR for <SCAR_No> for <AreaRequiringCA> has been changed from <AreaRequiringCA>.CurrentValue to <CarriageReturn> <CarriageReturn> to <AreaRequiringCA>.UpdatedValue.

SCAR_No field value (SCAR <number>) is an integer and a unique record identifier (primary key autonumber field) and <AreaRequiringCA> is a nvarchar(max) in design.

I have the code for the initial insert but do not know what to do with the  update for <AreaRequiringCA> field update.

No idea how to do this.  This email will occur like once in two weeks at most.


For a new record insert of a SCAR_No and emailing; here is the code that works fine.  Need one for the update event of the field <AreaRequiringCA> for a already existing SCAR_No.




use QA
go

CREATE TRIGGER [Email_SCARLogCopy] ON  [dbo].[SCARLogCopy]
   AFTER INSERT
AS
BEGIN

declare @number sysname;
set @number = (select SCAR_No from INSERTED);

DECLARE @sqlstr nvarchar(MAX)
SET @sqlstr = (SELECT AreaRequiringCA FROM inserted);
DECLARE @s_sub nvarchar(1000)
set @s_sub = 'New SCAR ' + @number+ ' was generated';
EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'SG',
      @recipients = 'sg@zl.com',
      @copy_recipients='sg@zl.com',
      @subject = @s_sub,
      @body =  @sqlstr
END
GO
ASKER CERTIFIED SOLUTION
Avatar of Dale Burrell
Dale Burrell
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David Todd
Hi,

Strongly suggest that you _not_ send the emails from a trigger.

Get the trigger to insert into a work table the relevant id's, and then get a SQL job to every minute or so scan the table for new ids, send the email, delete the id (or better, mark as sent)

If the trigger fires on a whole of table update, the waiting to send emails could be intolerable, and timeout, and the update/insert fail.

HTH
  David
Avatar of Sanjay

ASKER

I got this error:

Msg 4104, Level 16, State 1, Procedure Email_SCARLogCopy_Update, Line 12
The multi-part identifier "T.SCAR_No" could not be bound.

There is no T.SCAR_No
Should be I.SCAR_No - I couldn't test it, so leaving it to you to debug... just giving you a starting point.
Avatar of Sanjay

ASKER

Yeah I noticed that.  Thanks.
Avatar of Sanjay

ASKER

Thank you very much