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
357 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

911 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

23 Experts available now in Live!

Get 1:1 Help Now