CODEWITHSUNDEEP
c#sql-server-2008
Sreenath Ganga
Sreenath Ganga

Reputation: 736

Trigger or computed column?

I had a table employeeswipe_tbl in SQL Server 2008 with these columns:

create employeeswipe_tbl swipepk int (
swipepk int primary ,
empid  int,
dateofswipe date ,
intime datetime,
outime datetime,
logduration  time
)

When employee login in the intime, empid, dateofswipe is entered and when log out the outime is updated against swipepk

Now what I want is when I update outtime the logduration will be automatically calculated like

logduration  = outtime - intime

I am thinking of a some ideas like a computed column or a trigger. Can anyone give a good option considering I am a beginner ?

Upvotes: 0

Views: 95

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

A computed column is usually a better option than a trigger. Triggers can be disabled. A computed column is "always" correct:

create table employeeswipe_tbl (
swipepk int primary key,
empid  int,
intime datetime,
outime datetime,
dateofswipe AS CONVERT(date,intime) ,
logduration AS DATEDIFF(second,intime,outime)
)

As indicated in my comment, the difference between two datetimes would be a time span, not another datetime. There's no time span type in SQL Server, so just keeping the difference in seconds is probably best - do any formatting of it into hours, minutes and seconds during display. This will make it easier if you need to, for instance, add together several rows worth of data.

Side note - I was considering making dateofswipe a computed column also - isn't it just the date part of intime?

Upvotes: 2

Related Questions