Modify a column in 2 tables based on trigger

Question

This is SQL Server 2008r2. First, here are some tables and data for test:

CREATE TABLE [dbo].[MyOrders]
(
    [ID] [int] NOT NULL,
    [ref_type] [nchar](1) NOT NULL,
    [ref_num] [nvarchar](15) NULL,
    [req_cert] [nvarchar](255) NULL,
 CONSTRAINT [PK_MyOrders] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MyJobs]
(
    [job_id] [nvarchar](15) NOT NULL,
    [job_message] [nvarchar](255) NULL,
 CONSTRAINT [PK_MyJobs] PRIMARY KEY CLUSTERED 
(
    [job_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MyTypes]
(
    [type] [nvarchar](255) NOT NULL,
    [value] [nvarchar](255) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[MyOrders] ([ID], [ref_type], [ref_num], [req_cert])
VALUES (1, 'J', 'Job0001', 'Cert1')
GO

INSERT INTO [dbo].[MyJobs] ([job_id], [job_message])
VALUES ('Job0001', 'Accepted')
GO

INSERT INTO [dbo].[MyTypes] ([type], [value])
VALUES ('MyCerts', 'Cert1'),
       ('MyCerts', 'Cert2')
GO

Table MyOrders holds my orders, which can reference a job in table MyJobs. MyOrder can specifiy a req_cert, which then will display in job_message field. req_cert will have values from MyTypes table where type == 'MyCert'

What I am trying to do is create a trigger, which when the column req_cert or ref_num of MyOrders table gets updated, it will do the following:

1. Is one of those 2 fields updated?

2. Is ref_type == J and ref_num is not null?

3. Select the existing job_message and check if there is no value from MyTypes table.

4. If there is, replace it with value from req_cert

5. If there isn't, append req_cert

I wrote this trigger to do this:

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

    IF (NOT UPDATE ([req_cert])
        AND NOT UPDATE ([ref_num]))
       RETURN

    DECLARE @ID NVARCHAR(50)
    DECLARE @Certificate NVARCHAR(255)
    DECLARE @OldValue NVARCHAR(255)
    DECLARE @Found TINYINT
    DECLARE @JobMessage NVARCHAR(2000)

    DECLARE InsertCursor CURSOR FAST_FORWARD FOR
        SELECT ref_num, req_cert
        FROM Inserted

    OPEN InsertCursor

    FETCH NEXT FROM InsertCursor INTO @ID, @Certificate

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (NOT EXISTS (SELECT ref_num
                        FROM MyOrders
                        WHERE ref_type = 'J'
                          AND ref_num = @ID))
        BEGIN
            FETCH NEXT FROM InsertCursor INTO @ID, @Certificate
            CONTINUE
        END

        SELECT @JobMessage = job_message
        FROM MyJobs
        WHERE job_id = @ID

        DECLARE CertCursor CURSOR FAST_FORWARD FOR
             SELECT [Value]
             FROM MyTypes
             WHERE [Type] = 'MyCerts'

        OPEN CertCursor

        FETCH NEXT FROM CertCursor INTO @OldValue

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@JobMessage LIKE '%' + @OldValue + '%')
            BEGIN
                SET @Found = 1
                BREAK
            END

            FETCH NEXT FROM CertCursor INTO @OldValue
        END

        CLOSE CertCursor
        DEALLOCATE CertCursor

        IF (@Found = 1)
        BEGIN
            SELECT @JobMessage = REPLACE(@JobMessage, @OldValue, '')
        END

        UPDATE MyJobs WITH (ROWLOCK)
        SET job_message = ISNULL(@Certificate, '') + ISNULL(@JobMessage, '')
        WHERE MyJobs.job_id = @ID

        FETCH NEXT FROM InsertCursor INTO @ID, @Certificate
    END

    CLOSE InsertCursor
    DEALLOCATE InsertCursor

Expected results (assuming data from above):

UPDATE MyOrders
SET req_cert = 'Cert1'
WHERE ID = 1

job_message should be Cert1 Accepted

UPDATE MyOrders
    SET req_cert = 'Cert2'
    WHERE ID = 1

job message should be Cert2 Accepted

UPDATE MyOrders
SET ref_num = null
WHERE ID = 1
GO
UPDATE MyOrders
SET  req_cert = 'Cert1'
WHERE ID = 1
GO
UPDATE MyOrders
SET ref_num = 'Job0001'
WHERE ID = 1

job message should be Cert1 Accepted

My concern: the cursors part. The trigger is not working very well (as in it's slow), but I have no idea how to write it without them.

Answer 1

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 '% %'