Return to Answer

I'm not sure what is your final goal is, but doing that in triggers usually are not the best solution and they are not recommended to be used. By saying that I mean that there are situation when triggers can help you, but you need to use them with caution as there are quite a lot of troubles that triggers can bring if they are used wrongly.

Once again as I do not know what is the actual goal, I'll ask first before help you with a trigger. If the goal is to show the list with that text on UI, or track some kind of status, then I would suggest to join the tables and get the desired output. Maybe create a view (dbo.vMyOrders or any other name) and get the needed output, for example:

SELECT m.id, 
       m.ref_num, 
       mj.job_id, 
       req_cert 
       + IIF(mt.value IS NOT NULL, ' ' + job_message, '') AS cert_status 
FROM   dbo.myorders m 
       LEFT JOIN dbo.mytypes mt 
              ON mt.value = m.req_cert 
                 AND mt.type = 'MyCerts' 
       LEFT JOIN dbo.myjobs mj 
              ON mj.job_id = m.ref_num 
WHERE  ref_type = 'J' 
       AND ref_num IS NOT NULL 

If you still need trigger then:

 CREATE TRIGGER [dbo].[UpdateCert] 
    ON [dbo].[MyOrders]
    FOR UPDATE
    AS
        SET NOCOUNT ON
    
        IF (NOT UPDATE ([req_cert])
            AND NOT UPDATE ([ref_num]))
           RETURN
    
     UPDATE j
     SET job_message = req_cert + IIF(mt.value IS NOT NULL,' ' + job_message, '')
     FROM dbo.MyJobs j
     join inserted m on j.job_id = m.ref_num   
     LEFT JOIN dbo.MyTypes mt ON mt.value = m.req_cert AND mt.type = 'MyCerts'
     WHERE  ref_type = 'J' 
       AND ref_num IS NOT NULL

I'm not sure what is your final goal is, but doing that in triggers usually are not the best solution and they are not recommended to be used. By saying that I mean that there are situation when triggers can help you, but you need to use them with caution as there are quite a lot of troubles that triggers can bring if they are used wrongly.

Once again as I do not know what is the actual goal, I'll ask first before help you with a trigger. If the goal is to show the list with that text on UI, or track some kind of status, then I would suggest to join the tables and get the desired output. Maybe create a view (dbo.vMyOrders or any other name) and get the needed output, for example:

SELECT m.id, 
       m.ref_num, 
       mj.job_id, 
       req_cert 
       + IIF(mt.value IS NOT NULL, ' ' + job_message, '') AS cert_status 
FROM   dbo.myorders m 
       LEFT JOIN dbo.mytypes mt 
              ON mt.value = m.req_cert 
                 AND mt.type = 'MyCerts' 
       LEFT JOIN dbo.myjobs mj 
              ON mj.job_id = m.ref_num 
WHERE  ref_type = 'J' 
       AND ref_num IS NOT NULL
       AND job_message NOT LIKE '% %'

If you still need trigger then:

 CREATE TRIGGER [dbo].[UpdateCert] 
    ON [dbo].[MyOrders]
    FOR UPDATE
    AS
        SET NOCOUNT ON
    
        IF (NOT UPDATE ([req_cert])
            AND NOT UPDATE ([ref_num]))
           RETURN
    
     UPDATE j
     SET job_message = req_cert + IIF(mt.value IS NOT NULL,' ' + job_message, '')
     FROM dbo.MyJobs j
     join inserted m on j.job_id = m.ref_num   
     LEFT JOIN dbo.MyTypes mt ON mt.value = m.req_cert AND mt.type = 'MyCerts'
     WHERE  ref_type = 'J' 
       AND ref_num IS NOT NULL
       AND job_message NOT LIKE '% %'

I'm not sure what is your final goal is, but doing that in triggers usually are not the best solution and they are not recommended to be used. By saying that I mean that there are situation when triggers can help you, but you need to use them with caution as there are quite a lot of troubles that triggers can bring if they are used wrongly.

Once again as I do not know what is the actual goal, I'll ask first before help you with a trigger. If the goal is to show the list with that text on UI, or track some kind of status, then I would suggest to join the tables and get the desired output. Maybe create a view (dbo.vMyOrders or any other name) and get the needed output, for example:

SELECT m.id, 
       m.ref_num, 
       mj.job_id, 
       req_cert 
       + IIF(mt.value IS NOT NULL, ' ' + job_message, '') AS cert_status 
FROM   dbo.myorders m 
       LEFT JOIN dbo.mytypes mt 
              ON mt.value = m.req_cert 
                 AND mt.type = 'MyCerts' 
       LEFT JOIN dbo.myjobs mj 
              ON mj.job_id = m.ref_num 
WHERE  ref_type = 'J' 
       AND ref_num IS NOT NULL 

if you really need trigger for some reason let me know in the comments.

I'm not sure what is your final goal is, but doing that in triggers usually are not the best solution and they are not recommended to be used. By saying that I mean that there are situation when triggers can help you, but you need to use them with caution as there are quite a lot of troubles that triggers can bring if they are used wrongly.

Once again as I do not know what is the actual goal, I'll ask first before help you with a trigger. If the goal is to show the list with that text on UI, or track some kind of status, then I would suggest to join the tables and get the desired output. Maybe create a view (dbo.vMyOrders or any other name) and get the needed output, for example:

SELECT m.id, 
       m.ref_num, 
       mj.job_id, 
       req_cert 
       + IIF(mt.value IS NOT NULL, ' ' + job_message, '') AS cert_status 
FROM   dbo.myorders m 
       LEFT JOIN dbo.mytypes mt 
              ON mt.value = m.req_cert 
                 AND mt.type = 'MyCerts' 
       LEFT JOIN dbo.myjobs mj 
              ON mj.job_id = m.ref_num 
WHERE  ref_type = 'J' 
       AND ref_num IS NOT NULL 

If you still need trigger then:

 CREATE TRIGGER [dbo].[UpdateCert] 
    ON [dbo].[MyOrders]
    FOR UPDATE
    AS
        SET NOCOUNT ON
    
        IF (NOT UPDATE ([req_cert])
            AND NOT UPDATE ([ref_num]))
           RETURN
    
     UPDATE j
     SET job_message = req_cert + IIF(mt.value IS NOT NULL,' ' + job_message, '')
     FROM dbo.MyJobs j
     join inserted m on j.job_id = m.ref_num   
     LEFT JOIN dbo.MyTypes mt ON mt.value = m.req_cert AND mt.type = 'MyCerts'
     WHERE  ref_type = 'J' 
       AND ref_num IS NOT NULL

You haven't provided all DDL statements, so I tried to write the query without any test. CURSORS are quite slow for such operations. There are very rare situations when you actually need them when writing DML statements.

ALTER TRIGGER dbo.UpdateCert
ON dbo.MyOrders
FOR UPDATE
AS
SET NOCOUNT ON;

IF (NOT UPDATE(req_cert) AND NOT UPDATE(ref_num)) RETURN;

UPDATE m
   SET m.job_message = ISNULL(i.req_cert, '') + ISNULL(i.job_message, '')
  FROM MyJobs                                                                        AS m
  JOIN (   SELECT      i.ref_num
                     , i.req_cert
                     , COALESCE(REPLACE(m.job_message, mt.Value, ''), m.job_message) AS job_message
             FROM      Inserted AS i
             LEFT JOIN MyJobs   AS m ON m.job_id = i.ref_num
             LEFT JOIN MyTypes  AS mt ON mt.Type = 'MyCerts' AND m.job_message LIKE '%' + mt.Value + '%'
            WHERE      NOT EXISTS (   SELECT o.ref_num
                                        FROM MyOrders AS o
                                       WHERE o.ref_type = 'J' AND o.ref_num = i.ref_num)) AS i ON i.ref_num = m.job_id;

I'm not sure what is your final goal is, but doing that in triggers usually are not the best solution and they are not recommended to be used. By saying that I mean that there are situation when triggers can help you, but you need to use them with caution as there are quite a lot of troubles that triggers can bring if they are used wrongly.

Once again as I do not know what is the actual goal, I'll ask first before help you with a trigger. If the goal is to show the list with that text on UI, or track some kind of status, then I would suggest to join the tables and get the desired output. Maybe create a view (dbo.vMyOrders or any other name) and get the needed output, for example:

SELECT m.id, 
       m.ref_num, 
       mj.job_id, 
       req_cert 
       + IIF(mt.value IS NOT NULL, ' ' + job_message, '') AS cert_status  
FROM   dbo.myorders m 
       LEFT JOIN dbo.mytypes mt 
              ON mt.value = m.req_cert 
                 AND mt.type = 'MyCerts' 
       LEFT JOIN dbo.myjobs mj  
              ON mj.job_id = m.ref_num 
WHERE  ref_type = 'J'  
       AND ref_num IS NOT NULL 

if you really need trigger for some reason let me know in the comments.