Help with Computed Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Help with Computed Column

Author

Topic

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-12-09 : 10:06:27

Hi
I have 2 columns in a table [date] and [week].
i have converted week into a computed column using datepart(week,[date])
So when a date is entered it automatically calculates the calendar week, this works absolutely fine.
However if i update a set of data which has the week number but not the date, due to the computed column i loose the week number.
How do i retain the week number if the given date is NULL?

Thanks for your assistance in advance.

Ewan Gilby

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 10:32:36

You mean you wanted to update the date to null but retain the old week number?
Don't use a computed column - maintain the value in a trigger then you can only update if the new value is not null.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-12-09 : 10:50:54

Hi thanks for your reply. i dont know how to create a trigger.
Could you help me with the syntax please.

Ewan Gilby

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-09 : 11:56:21

Create Trigger

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-09 : 12:07:02

you can check for whether week field was included as a part of update operation using UPDATE() function inside trigger and if its false explicitly do update inside trigger to make it datepart(week,date)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 14:27:41

create trigger tr_tbl on tbl for update
as
update tbl
set weeknumber = datepart(wk,date)
from tbl t
join inserted i
on t.pk = i.pk
where i.date is not null
go

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources