Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
?
383 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.
Suggested Courses

581 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