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.

• Yuropoor: Can I know why the downvotes? What else should be added?
• Mast: 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.
• Yuropoor: 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.

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

• Yuropoor: Which statements are not provided? Also, thank you for the answer, but this fails test case.
• Dmitrij Kultasev: my bad. copy/paste error. Will review this a bit later
• Yuropoor: 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.
• Yuropoor: After testing the new version, still fails the test cases, sorry :)
• Dmitrij Kultasev: updated queries, however it is not clear what to do when the value is updated back to NULL