sql - How to have a table trigger get the current row PK -

i trying set trigger table update column "duration" duration between start , end date on row. however, whenever insert/update column, trigger not work because scope_indentity() keeps returning null. how can fix this?

alter trigger [dbo].[calcduration] on [dbo].[event] after insert, update, delete   declare @started datetime declare @finished datetime declare @id int  select @id = scope_identity()  select @started  = [date & time started]  dbo.event [event id] = @id select @finished = [date & time finished] dbo.event [event id] = @id  update dbo.event  set duration = datediff (  hour, @started, @finished ) [event id] = @id 

scope_indentity() returns last inserted identity value within current scope, in case it's used inside trigger return value after insert operation performed in trigger - not case here , therefore returns null.

i suppose trying id of inserted/updated value in case table inserted can used.

next, making big mistake assuming trigger going work on single row, not case, because update , insert can work on multiple rows. inserted table luckily contains rows inserted/updated.

so trigger should this:

update e set e.duration = datediff (hour, e.[date & time started], e.[date & time finished]) dbo.event e inner join inserted on e.[event id] = i.[event id] 

and @ last, point unnecessary use of triggers. same can done (and should be) using computed column. if change column to:

alter table  dbo.event alter column [duration] datediff(hour, [date & time started], [date & time finished]) 

you have correct duration without need trigger.