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

LapchienDirectorAsked:
Who is Participating?
 
LapchienConnect With a Mentor DirectorAuthor 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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
chrismcConnect With a Mentor Commented:
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.