Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
381 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 40

Accepted Solution

by:
lcohan earned 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

885 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