0
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ Can I know why the downvotes? What else should be added? \$\endgroup\$ – Yuropoor Jan 21 at 11:30
  • \$\begingroup\$ Currently your question is attracting close votes for lack of context, being too broad and the question being unclear. While I'm not sure the question deserves to be closed, it can definitely be improved. Please take a good look at our FAQ on asking questions for quick fixes and general improvements. \$\endgroup\$ – Mast Jan 21 at 12:59
  • 1
    \$\begingroup\$ I have the example data, test case, actual code, my concern and description. I don't see anything more than I could add about it. \$\endgroup\$ – Yuropoor Jan 21 at 13:14
0
\$\begingroup\$

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 '% %'
\$\endgroup\$
  • 1
    \$\begingroup\$ Which statements are not provided? Also, thank you for the answer, but this fails test case. \$\endgroup\$ – Yuropoor Jan 23 at 16:25
  • \$\begingroup\$ my bad. copy/paste error. Will review this a bit later \$\endgroup\$ – Dmitrij Kultasev Jan 23 at 17:07
  • \$\begingroup\$ The goal is to get the proper value into the job_message field. This field is bound to a form control in ERP system, and cannot be changed. \$\endgroup\$ – Yuropoor Jan 23 at 21:17
  • \$\begingroup\$ After testing the new version, still fails the test cases, sorry :) \$\endgroup\$ – Yuropoor Jan 28 at 5:55
  • \$\begingroup\$ updated queries, however it is not clear what to do when the value is updated back to NULL \$\endgroup\$ – Dmitrij Kultasev Jan 28 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.