asked on

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

TonyReba

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

Lapchien

ASKER

Thanks, I can create the trigger okay, I just need some assistance with the sql to actually perform the task.

SOLUTION

Chris McGuigan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lapchien

ASKER

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)

Lapchien

ASKER

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

ASKER CERTIFIED SOLUTION

Lapchien

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lapchien

ASKER

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

Lapchien

ASKER

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

Chris McGuigan

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

modus_operandi

Starting auto-close process to implement the recommendations of the participating Expert(s).

modus_operandi
EE Admin

Lapchien

ASKER

Yes I agree