SQL update trigger locks up table access

I have a table that I need to update the values in a column based on the values in 2 other columns of the table.  I've been able to accomplish this with a table trigger but it degredates the SQL performance to the point where other users/applications can't write to the table/db.  I'd appreciate any information that would help me remedy the issue.  I'd also be open to "applying" the update when the record is inserted.  The issue is that this is aged, proprietary software that I can't get support for.  He is the code for my trigger.

SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UpdateEntryTime] ON [LablitePC].[dbo].[InprogressResults]
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE  InProgressResults
SET     IPR_EntryTime = CASE
WHEN SH_Key = 1123 then IPR_EntryDate
WHEN SH_Key = 1124 then IPR_EntryDate + '03:00'
WHEN SH_Key = 1125 then IPR_EntryDate + '06:00'
WHEN SH_Key = 1126 then IPR_EntryDate + '09:00'
WHEN SH_Key = 1127 then IPR_EntryDate + '12:00'
WHEN SH_Key = 1128 then IPR_EntryDate + '15:00'
WHEN SH_Key = 1129 then IPR_EntryDate + '18:00'
WHEN SH_Key = 1130 then IPR_EntryDate + '21:00'
WHEN SH_Key = 1142 then IPR_EntryDate + '01:00'
WHEN SH_Key = 1143 then IPR_EntryDate + '02:00'
WHEN SH_Key = 1144 then IPR_EntryDate + '04:00'
WHEN SH_Key = 1145 then IPR_EntryDate + '05:00'
WHEN SH_Key = 1146 then IPR_EntryDate + '07:00'
WHEN SH_Key = 1147 then IPR_EntryDate + '08:00'
WHEN SH_Key = 1148 then IPR_EntryDate + '10:00'
WHEN SH_Key = 1149 then IPR_EntryDate + '22:00'
WHEN SH_Key = 1150 then IPR_EntryDate + '20:00'
WHEN SH_Key = 1151 then IPR_EntryDate + '19:00'
WHEN SH_Key = 1152 then IPR_EntryDate + '17:00'
WHEN SH_Key = 1153 then IPR_EntryDate + '16:00'
WHEN SH_Key = 1154 then IPR_EntryDate + '14:00'
WHEN SH_Key = 1155 then IPR_EntryDate + '13:00'
WHEN SH_Key = 1156 then IPR_EntryDate + '11:00'
WHEN SH_Key = 1157 then IPR_EntryDate + '23:00'
WHEN SH_Key = 1173 then IPR_EntryDate + '01:30'
WHEN SH_Key = 1174 then IPR_EntryDate + '04:30'
WHEN SH_Key = 1175 then IPR_EntryDate + '07:30'
WHEN SH_Key = 1176 then IPR_EntryDate + '10:30'
WHEN SH_Key = 1177 then IPR_EntryDate + '13:30'
WHEN SH_Key = 1178 then IPR_EntryDate + '16:30'
WHEN SH_Key = 1179 then IPR_EntryDate + '19:30'
WHEN SH_Key = 1180 then IPR_EntryDate + '22:30'
ELSE IPR_EntryDate
END
END
dsmith43Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You're UPDATEing every row in the table every time.  You need to join to the inserted table to only update the new/modified row(s) for that trigger.

I'm not sure of the key column(s) on the table, you'll need to change the <> placeholders to the correct names in the code below.

NOTE: Do not change the name in the UPDATE to the actual table name, leave it the alias name!  When using joins in an UPDATE, you should always update an alias name, not the base table name.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER TRIGGER [dbo].[UpdateEntryTime]
ON [LablitePC].[dbo].[InprogressResults]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE  IPR
SET     IPR_EntryTime = CASE
WHEN SH_Key = 1123 then IPR_EntryDate
WHEN SH_Key = 1124 then IPR_EntryDate + '03:00'
WHEN SH_Key = 1125 then IPR_EntryDate + '06:00'
WHEN SH_Key = 1126 then IPR_EntryDate + '09:00'
WHEN SH_Key = 1127 then IPR_EntryDate + '12:00'
WHEN SH_Key = 1128 then IPR_EntryDate + '15:00'
WHEN SH_Key = 1129 then IPR_EntryDate + '18:00'
WHEN SH_Key = 1130 then IPR_EntryDate + '21:00'
WHEN SH_Key = 1142 then IPR_EntryDate + '01:00'
WHEN SH_Key = 1143 then IPR_EntryDate + '02:00'
WHEN SH_Key = 1144 then IPR_EntryDate + '04:00'
WHEN SH_Key = 1145 then IPR_EntryDate + '05:00'
WHEN SH_Key = 1146 then IPR_EntryDate + '07:00'
WHEN SH_Key = 1147 then IPR_EntryDate + '08:00'
WHEN SH_Key = 1148 then IPR_EntryDate + '10:00'
WHEN SH_Key = 1149 then IPR_EntryDate + '22:00'
WHEN SH_Key = 1150 then IPR_EntryDate + '20:00'
WHEN SH_Key = 1151 then IPR_EntryDate + '19:00'
WHEN SH_Key = 1152 then IPR_EntryDate + '17:00'
WHEN SH_Key = 1153 then IPR_EntryDate + '16:00'
WHEN SH_Key = 1154 then IPR_EntryDate + '14:00'
WHEN SH_Key = 1155 then IPR_EntryDate + '13:00'
WHEN SH_Key = 1156 then IPR_EntryDate + '11:00'
WHEN SH_Key = 1157 then IPR_EntryDate + '23:00'
WHEN SH_Key = 1173 then IPR_EntryDate + '01:30'
WHEN SH_Key = 1174 then IPR_EntryDate + '04:30'
WHEN SH_Key = 1175 then IPR_EntryDate + '07:30'
WHEN SH_Key = 1176 then IPR_EntryDate + '10:30'
WHEN SH_Key = 1177 then IPR_EntryDate + '13:30'
WHEN SH_Key = 1178 then IPR_EntryDate + '16:30'
WHEN SH_Key = 1179 then IPR_EntryDate + '19:30'
WHEN SH_Key = 1180 then IPR_EntryDate + '22:30'
ELSE IPR_EntryDate
END
FROM dbo.InprogressResults IPR
INNER JOIN inserted i ON i.<key_column> = IPR.<key_column> /* AND i.<key_column2> = IPR.<key_column2> */
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
Is that a big table? It looks like you are updating the full InProgressResults table and not just the records that are being inserted/updated.

try changing:
UPDATE  InProgressResults

Open in new window

for:
UPDATE  inserted

Open in new window

0
Russ SuterCommented:
You can accomplish the same result without a trigger. Combine this with an index and you'll probably get much better performance.

1. Create an index on SH_Key (and nothing else) if it does not already exist.
2. Change IPR_EntryDate to a computed column using the logic from the above trigger.

One caveat. This only works if IPR_EntryDate is not a column that has its value changed later on due to some other operation. You cannot perform UPDATE ... SET ... on a computed column.
1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

dsmith43Author Commented:
I get this message when I changed "UPDATE InProgressResults" to "UPDATE inserted."

Msg 286, Level 16, State 1, Procedure UpdateEntryTime, Line 7
The logical tables INSERTED and DELETED cannot be updated.
0
Éric MoreauSenior .Net ConsultantCommented:
you are right. use the syntax of Scott
0
dsmith43Author Commented:
Thanks for the help guys.  I'm using Scott's syntax but now I'm getting this message for each line.

Msg 209, Level 16, State 1, Procedure UpdateEntryTime, Line 8
Ambiguous column name 'SH_Key'.
Msg 209, Level 16, State 1, Procedure UpdateEntryTime, Line 8
Ambiguous column name 'SH_Key'.
0
dsmith43Author Commented:
I figured that part out.  Here is the final code.  Thanks for the help.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER TRIGGER [dbo].[UpdateEntryTime]
ON [LablitePC].[dbo].[InprogressResults]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE  IPR
SET     IPR_EntryTime = CASE
WHEN IPR.SH_Key = 1123 then IPR.IPR_EntryDate
WHEN IPR.SH_Key = 1124 then IPR.IPR_EntryDate + '03:00'
WHEN IPR.SH_Key = 1125 then IPR.IPR_EntryDate + '06:00'
WHEN IPR.SH_Key = 1126 then IPR.IPR_EntryDate + '09:00'
WHEN IPR.SH_Key = 1127 then IPR.IPR_EntryDate + '12:00'
WHEN IPR.SH_Key = 1128 then IPR.IPR_EntryDate + '15:00'
WHEN IPR.SH_Key = 1129 then IPR.IPR_EntryDate + '18:00'
WHEN IPR.SH_Key = 1130 then IPR.IPR_EntryDate + '21:00'
WHEN IPR.SH_Key = 1142 then IPR.IPR_EntryDate + '01:00'
WHEN IPR.SH_Key = 1143 then IPR.IPR_EntryDate + '02:00'
WHEN IPR.SH_Key = 1144 then IPR.IPR_EntryDate + '04:00'
WHEN IPR.SH_Key = 1145 then IPR.IPR_EntryDate + '05:00'
WHEN IPR.SH_Key = 1146 then IPR.IPR_EntryDate + '07:00'
WHEN IPR.SH_Key = 1147 then IPR.IPR_EntryDate + '08:00'
WHEN IPR.SH_Key = 1148 then IPR.IPR_EntryDate + '10:00'
WHEN IPR.SH_Key = 1149 then IPR.IPR_EntryDate + '22:00'
WHEN IPR.SH_Key = 1150 then IPR.IPR_EntryDate + '20:00'
WHEN IPR.SH_Key = 1151 then IPR.IPR_EntryDate + '19:00'
WHEN IPR.SH_Key = 1152 then IPR.IPR_EntryDate + '17:00'
WHEN IPR.SH_Key = 1153 then IPR.IPR_EntryDate + '16:00'
WHEN IPR.SH_Key = 1154 then IPR.IPR_EntryDate + '14:00'
WHEN IPR.SH_Key = 1155 then IPR.IPR_EntryDate + '13:00'
WHEN IPR.SH_Key = 1156 then IPR.IPR_EntryDate + '11:00'
WHEN IPR.SH_Key = 1157 then IPR.IPR_EntryDate + '23:00'
WHEN IPR.SH_Key = 1173 then IPR.IPR_EntryDate + '01:30'
WHEN IPR.SH_Key = 1174 then IPR.IPR_EntryDate + '04:30'
WHEN IPR.SH_Key = 1175 then IPR.IPR_EntryDate + '07:30'
WHEN IPR.SH_Key = 1176 then IPR.IPR_EntryDate + '10:30'
WHEN IPR.SH_Key = 1177 then IPR.IPR_EntryDate + '13:30'
WHEN IPR.SH_Key = 1178 then IPR.IPR_EntryDate + '16:30'
WHEN IPR.SH_Key = 1179 then IPR.IPR_EntryDate + '19:30'
WHEN IPR.SH_Key = 1180 then IPR.IPR_EntryDate + '22:30'
ELSE IPR.IPR_EntryDate
END
FROM dbo.InprogressResults IPR
INNER JOIN inserted i ON i.IPR_KEY = IPR.IPR_KEY
GO
0
dsmith43Author Commented:
Thanks so much for your assistance.  I typically like to figure out things like this on my own but I needed some expert help this time.  Thanks again.
0
Scott PletcherSenior DBACommented:
You're welcome, I'm very glad it helped.  Sorry, I should have noticed that adding "inserted" would cause ambiguous column name(s).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.