Sanjay
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>.CurrentV alue to <CarriageReturn> <CarriageReturn> to <AreaRequiringCA>.UpdatedV alue.
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.co m',
@subject = @s_sub,
@body = @sqlstr
END
GO
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.co
@subject = @s_sub,
@body = @sqlstr
END
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
ASKER
Yeah I noticed that. Thanks.
ASKER
Thank you very much
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