Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
361 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
ID: 39967303
"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
ID: 39967325
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
ID: 39967617
Thanks.  Works after I tweaked the current coding per your recommendation.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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