UPDATE trigger causes deadlock I have a After Update trigger where trigger insert a value in same table to find a duration column.My goal is whenever end_time value is inserted then trigger will find duration in hh:mm:ss format and insert into duration column.
Problem- Trigger causes so many deadlock therefore blocking another process.

Column datatype informatiom..
Start_time datetime, End_time datetime, Duration time(3)

ALTER TRIGGER [dbo].[GET_DURATION] ON [dbo].[INBOUND_CALL_xxx]
AFTER UPDATE AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Begin Transaction

               UPDATE INBOUND_CALL_xxx
               SET DURATION = convert(time(0),(END_TIME- START_TIME))                                                     
               FROM INBOUND_CALL_xxx with (nolock)
Commit;
You are missing the reference to the special table inserted which holds just the rows that were updated. Currently your trigger is updating all the rows of the tracking table, every time.

ALTER TRIGGER [dbo].[GET_DURATION] ON [dbo].[INBOUND_CALL_xxx]
AFTER UPDATE AS
BEGIN

    IF UPDATE(END_TIME) OR UPDATE(START_TIME)
    BEGIN

               UPDATE C
               SET DURATION = convert(time(0),(I.END_TIME - I.START_TIME))                                                     
               FROM 
                inserted AS I 
                INNER JOIN INBOUND_CALL_xxx AS C ON I.KeyColumn = C.KeyColumn

    END

END


You can also modify the trigger to update the DURATION only if the relevant columns were updated (as of this example).



Another option would be using a computed column (maybe persisted) with an expression of CONVERT(time(0), END_TIME - START_TIME), so you don't need a trigger to keep the values updated.

ALTER TABLE INBOUND_CALL_xxx DROP COLUMN DURATION

ALTER TABLE INBOUND_CALL_xxx ADD DURATION AS CONVERT(TIME(0), END_TIME - START_TIME) -- PERSISTED (if needed)