SQL update trigger locks up table access

dsmith43
dsmith43 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
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

Russ SuterSenior Software Developer

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
you are right. use the syntax of Scott

Author

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

Author

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

Author

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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You're welcome, I'm very glad it helped.  Sorry, I should have noticed that adding "inserted" would cause ambiguous column name(s).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial