Default Column Computed Value - 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

General SQL Server Forums

New to SQL Server Programming

Default Column Computed Value

Author

Topic

dambo5
Starting Member

12 Posts

Posted - 2011-10-21 : 06:57:31

Dear All,
In our solution we have a column on every table in the DB that logs the SYSTEM_USER that has created/updated the row.
We do this using default:
CREATE DEFAULT [dbo].[A_User] AS SYSTEM_USER
GO

I would like to keep an Id(int) instead of a varchar since it occupies a lot of space in the DB.
The problem is as follows:
- I cannot call procedure (exec proc) from default definition?
- I cannot use undocumented sp_MS_marksystemobject to create system function (only procedure or table), that I'll further call
- I cannot use (or don't know how) the computed column since as I understand it can just compute(manipulate) values from within the current table

Simple question would be, how to do a default on a column that will do select id from other_lookup_table where user=SYSTEM_USER

What will be the most elegant way of solving this?
Thank you.
Aleksandar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 08:20:55

i think you can add a default constraint on table based on a udf. inside udf you will return the id value based on user.

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

dambo5
Starting Member

12 Posts

Posted - 2011-10-21 : 10:08:42

Only system functions like getdate() or such?
Now I do not know how to define my UDF as system (global) function.

Am I getting something wrong?

CREATE DEFAULT [dbo].[AAA] AS dbo.CreateDate(2011,6,15)
GO

Msg 4105, Level 16, State 1, Procedure AAA, Line 1
User-defined functions, partition functions, and column references are not allowed in expressions in this context.

select dbo.CreateDate(2011,6,15) works good in the same database

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 10:32:34

nope not as global default
you can link it to a column though

column <datatype> <nullability> DEFAULT dbo.UDF()...

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

dambo5
Starting Member

12 Posts

Posted - 2011-10-21 : 17:57:35

This will do the job. THANK YOU!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 04:45:14

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources