CODEWITHSUNDEEP
sqlsql-server-2008triggers
user1596993
user1596993

Reputation: 21

SQL AFTER Insert Trigger for Update

I am trying to update the lunTot column after lunOut column value is inserted by using a trigger, but somehow it's not updating. If I use Update statement query it's working well.

After insert trigger (not working):

create trigger trg_AfterInsertLunTot
on lunTime
after insert
as
begin
    update lunTime
    set lunTot = DATEDIFF(minute, lunIn, lunOut) 
    from lunTime
end
go

With Update query (working)

update lunTime
set lunTot = DATEDIFF(minute, lunIn, lunOut) 
from lunTime

Upvotes: 1

Views: 112

Answers (1)

marc_s
marc_s

Reputation: 754598

Why don't you just make lunTot a computed column? No need to use a trigger for this....

Just drop your existing lunTot column you have now

ALTER TABLE dbo.lunTime
DROP COLUMN lunTot

and then define it again as a computed column:

ALTER TABLE lunTime 
ADD lunTot = DATEDIFF(minute, lunIn, lunOut)

Now your column lunTot will always reflect the difference between lunIn and lunOut - for all existing row, and automagically after you insert new rows as well.

Upvotes: 2

Related Questions