Tukukkk

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, but I don't know if that's the best way:

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