Solved

ms sql code to store data in a temp table first and then email the data to recipients

Posted on 2014-03-31
3
353 Views
Last Modified: 2014-03-31
I have code below that emails the recipient once a new SCAR_No is generated or when the "AreaRequiringCA" field value changes.  I have been told not to email directly but to store this record in some temp table and then email from that temp table and then drop the temp table.  Not sure how to accomplish this.  The code below works.  Guess I need expert help to build on the script below to insert the new or changed data into a temp table and then email it out and drop the table.



use QA
go

CREATE TRIGGER [Email_SCARLogCopy_Insert_Update] ON  [dbo].[SCARLogCopy]
   AFTER INSERT, UPDATE
AS
BEGIN
  set nocount on

  declare @SCAR_No varchar(38), @s_sub nvarchar(1000), @sqlstr nvarchar(MAX)

  declare @updates table (SCAR_No varchar(38), AreaRequiringCA nvarchar(max), AreaRequiringCAOld nvarchar(max), IsInsert bit)

  insert into @Updates (SCAR_No, AreaRequiringCA, AreaRequiringCAOld, IsInsert)
    select I.SCAR_No, I.AreaRequiringCA, D.AreaRequiringCA, case when D.SCAR_No is null then 1 else 0 end
    from Inserted I
    left join Deleted D on D.SCAR_No = I.SCAR_No

  while exists (select 1 from @Updates) begin
    select top 1 @SCAR_No = SCAR_No
      , @sqlstr = case when IsInsert = 1 then 'Area Requiring CA: ' + AreaRequiringCA else 'SCAR-' + SCAR_No + ' AreaRequiringCA has been changed FROM: ' + char(13) + char(13) + AreaRequiringCAOld + char(13) + char(13) + 'TO' + char(13) + char(13) + AreaRequiringCA end
      , @s_sub = case when IsInsert = 1 then 'New SCAR ' + SCAR_No+ ' was generated' else 'Updated SCAR ' + SCAR_No + ' Area Requiring CA' end
    from @Updates
    exec msdb.dbo.sp_send_dbmail
            @profile_name = 'SG',
            @recipients = 'sg@x.com',
            @copy_recipients='sg@x.com',
            @subject = @s_sub,
            @body =  @sqlstr
    delete from @Updates where @SCAR_No = SCAR_No
  end
END
GO
0
Comment
Question by:sxxgupta
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
"I have been told not to email directly but to store this record in some temp table and then email from that temp table"

You shoul in my opinion add a new "working queue" table called SCAR_MailQueue  for instance having the same structure as the data inserted in the trigger:
...
insert into @Updates (SCAR_No, AreaRequiringCA, AreaRequiringCAOld, IsInsert)
...

You could store the record from the trigger into this lets call it SCAR_MailQueue table. Now you will need here either a timestamp or a IsProcessed flag column so you can flip the processed records to TRUE and this way mail will not be re-sent every time you parse it.

Create a stored proc to parse this SCAR_MailQueue where IsProcessed = 0 (FALSE) and send the mails one by one in a cursor for instance. At the end of each processed mail, make sure you flip the IsProcessed = 1 (TRUE)  for that record only.

You can add a scheduled job to run this SPROC at desired frequency.
0
 

Author Comment

by:sxxgupta
Comment Utility
Thanks lcohan.  But not sure how to script what you are suggesting.  Another expert had help me script the posted script.....

S
0
 

Author Closing Comment

by:sxxgupta
Comment Utility
Thanks.  Works after I tweaked the current coding per your recommendation.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 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

21 Experts available now in Live!

Get 1:1 Help Now