Your trigger looks too complex to perform well under load.

In this case it looks as though it can be made set based.

An alternative would be to make the processing asynchronous either by using Service Broker or by rolling your own queue.

(Google if you are interested.)

Try something like the following:

ALTER TRIGGER [Event].[trUpdateActivityStatusAfterUpdate]

ON [Event].[ServiceAreaActivityProcess]

AFTER UPDATE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

IF (SELECT TRIGGER_NESTLEVEL() ) > 1

RETURN;

DECLARE @CurrentDate date = CURRENT_TIMESTAMP;

WITH AStatii

AS

(

SELECT [CURRENT],[ODUENCOMP],[COMPLETE],[ODUECOMP],[EXTCOMP]

FROM

(

SELECT ActivityStatusId, ActivityStatusCode

FROM #ActivityStatus

) S

PIVOT

(

MAX(ActivityStatusId)

FOR ActivityStatusCode IN ([CURRENT],[ODUENCOMP],[COMPLETE],[ODUECOMP],[EXTCOMP])

) P

)

UPDATE P

SET ActivityStatusId =

CASE

WHEN P.CompletionDate IS NOT NULL

AND P.ExtDueDate IS NOT NULL

AND P.CompletionDate <= P.ExtDueDate

THEN S.EXTCOMP

WHEN P.CompletionDate IS NOT NULL

AND P.CompletionDate <= P.DueDate

THEN S.COMPLETE

WHEN P.CompletionDate IS NOT NULL

THEN S.ODUECOMP

WHEN P.ExtDueDate IS NOT NULL

AND P.ExtDueDate <= @CurrentDate

THEN S.[CURRENT]

WHEN P.ExtDueDate IS NOT NULL

THEN S.ODUENCOMP

ELSE P.ActivityStatusId

END

FROM [Event].[ServiceAreaActivityProcess] P

CROSS JOIN AStatii S

WHERE EXISTS

(

SELECT 1

FROM inserted I

-- Assuming ServiceAreaActivityProcessId is the PK of [Event].[ServiceAreaActivityProcess]

WHERE I.ServiceAreaActivityProcessId = P.ServiceAreaActivityProcessId

);

END