[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

SQL Insert and then insert to another table

I want to insert a record to another table (JobQueue) when a record is inserted into table (Completions).

The code is attached, but I don't think it is in the correct order for a trigger.

Can anyone assist with this please?

Thanks

Lapchien

DECLARE @JobId int
      DECLARE @StatusId int
      DECLARE @QueueData varchar(MAX)
      DECLARE @EmailAddress varchar(255)
      DECLARE @JobQueueId int
      DECLARE @TradeCode char(1)
      DECLARE @CustomerName varchar(50)
      DECLARE @EmailSurveyInvitation varchar(MAX)

      
      SELECT 
            @JobId = J.JobId,
            @StatusId = J.StatusId, 
            @EmailAddress = J.EmailAddress,
            @EmailSurveyInvitation = (SELECT Body FROM JobQueueEmailTemplates WHERE EmailTemplateId = 3),
            @TradeCode = J.TradeCode,
            @CustomerName = J.CustomerTitle + ' ' + J.CustomerSurname
      FROM 
			inserted i
            INNER JOIN Jobs J on J.JobId = i.JobId
      
      WHERE J.EmailAddress IS NOT NULL
      
      
      
      -- TASK: Email survey invitation
      IF (@EmailAddress IS NOT NULL)
            BEGIN
            SET @QueueData = 
            'recipient=' + ISNULL(@EmailAddress, '') + ';' +
            'sender=me@mydomain.co.uk;' +
            'subject=Thank you for your booking booking;' +
            'html=1;' +
            'body=<![CDATA[' + @EmailSurveyInvitation + ']]>'

            SET @QueueData = REPLACE(@QueueData, '#ClientName#', @CustomerName)
       
      
            INSERT JobQueue (Action, JobId, QueueData) 
                  SELECT 
                  'G', 
                  @JobId,
                  @QueueData                                            
                        
            SELECT @JobQueueId = SCOPE_IDENTITY()
			END

Open in new window

0
Lapchien
Asked:
Lapchien
2 Solutions
 
TonyRebaCommented:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [name]
   ON  [TableName]
AFTER INSERT
AS
BEGIN

--YOUR PROC GOES HERE--


END
0
 
LapchienDirectorAuthor Commented:
Thanks, I can create the trigger okay, I just need some assistance with the sql to actually perform the task.
0
 
chrismcCommented:
This appears OK on the face of it but some words of warning;

You are assuming that the trigger is only going to get one record at a time. A common mistake but it's likely to catch you out at some point.

You'd be better off with a statement like the one below;

This will work for multiple records too.
If you need the Id, do the scope_identity in the code doing the original insert, not the trigger because you can't pass @JobQueueId back from the trigger.

Hope that helps?
INSERT JobQueue (Action, JobId, QueueData) 
	SELECT 'G', J.JobId, 
		Replace('recipient=' + ISNULL(@EmailAddress, '') + ';' +
		'sender=me@mydomain.co.uk;' +
		'subject=Thank you for your booking booking;' +
		'html=1;' +
		'body=<![CDATA[' + @EmailSurveyInvitation + ']]>', '#ClientName#', J.CustomerTitle + ' ' + J.CustomerSurname)
		FROM inserted i
			JOIN Jobs J On J.JobId = i.JobId
		WHERE J.EmailAddress IS NOT NULL

Open in new window

0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LapchienDirectorAuthor Commented:
Thanks, makes sense.

Where do I declare (and set) @EmailAddress and @EmailSurveyInvitation ?


The table being inserted into is called Completions, it needs to be joined to table Jobs on JobId, the email address comes from Jobs.EmailAddress.

The @EmailSurveyInvitation comes from @EmailSurveyInvitation = (SELECT Body FROM JobQueueEmailTemplates WHERE EmailTemplateId = 3)
0
 
LapchienDirectorAuthor Commented:
Something like this?:

INSERT JobQueue (Action, JobId, QueueData)
 
      SELECT
            'G',
            inserted.JobId,
            Replace('recipient=' + ISNULL(inserted.EmailAddress, '') + ';' +
            'sender=me@mydomain.co.uk;' +
            'subject=Thank you for your booking booking;' +
            'html=1;' +
            'body=<![CDATA[' + (SELECT Body FROM JobQueueEmailTemplates WHERE EmailTemplateId = 3) + ']]>', '#ClientName#', J.CustomerTitle + ' ' + J.CustomerSurname)
            FROM inserted
                  INNER JOIN Jobs J On J.JobId = inserted.JobId
                  
                  
            WHERE inserted.EmailAddress IS NOT NULL

0
 
LapchienDirectorAuthor Commented:
Sorry, or as amended:



INSERT JobQueue (Action, JobId, QueueData)

      SELECT
            'G',
            J.JobId,
            Replace('recipient=' + ISNULL(J.EmailAddress, '') + ';' +
            'sender=me@mydomain.co.uk;' +
            'subject=Thank you for your booking booking;' +
            'html=1;' +
            'body=<![CDATA[' + (SELECT Body FROM JobQueueEmailTemplates WHERE EmailTemplateId = 3) + ']]>', '#ClientName#', J.CustomerTitle + ' ' + J.CustomerSurname)
            FROM inserted
                  INNER JOIN Jobs J On J.JobId = J.JobId
                        
            WHERE J.EmailAddress IS NOT NULL
0
 
LapchienDirectorAuthor Commented:
Just waiting for an insert to test this, but I think it looks okay:-



INSERT JobQueue (Action, JobId, QueueData)

      SELECT
            'G',
            J.JobId,
            Replace('recipient=' + ISNULL(J.EmailAddress, '') + ';' +
            'sender=plumbing@tradetek.co.uk;' +
            'subject=Thank you for your booking with TradeTek;' +
            'html=1;' +
            'body=<![CDATA[' + (SELECT Body FROM JobQueueEmailTemplates WHERE EmailTemplateId = 3) + ']]>', '#ClientName#', J.CustomerTitle + ' ' + J.CustomerSurname)
            FROM
                  inserted i
                  INNER JOIN Jobs J On J.JobId = i.JobId
                        
            WHERE
                  J.EmailAddress IS NOT NULL
                  AND
                  i.InProgress = 0

0
 
LapchienDirectorAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Lapchien's comment http:/Q_27394937.html#36962686

for the following reason:

managed to work it out in the end
0
 
chrismcCommented:
That last one looked right. But did you mean to close this and accept your own answer even though it is based heavily on my answer?

Cheers
Chris
0
 
modus_operandiCommented:
Starting auto-close process to implement the recommendations of the participating Expert(s).
 
modus_operandi
EE Admin
0
 
LapchienDirectorAuthor Commented:
Yes I agree
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now