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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Computed column question

Author  Topic 

SamC
White Water Yakist

3467 Posts

Posted - 2014-10-13 : 09:02:24
I've got a table in an existing web app. I'd like to add a new column which is computed by an internal UDF function.

I am able to do this easily using a trigger when the column is set to NOT NULL DEFAULT ''.

Here's the catch: I'd like to make this new column UNIQUE NOT NULL, no default value. Since the trigger is fired only after the insert is attempted, the INSERT will fail because there is no value for the new column which has a UNIQUE NOT NULL constraint.

Is there a technique using trigger or another method to insert the computed value simultaneously with the insert.

Sorry if this has been asked before. Wasn't sure what to search for.

Sam

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-13 : 09:45:00
You could try to use an INSTEAD OF trigger rather than AFTER trigger, which would be invoked before the constraints are evaluated. http://technet.microsoft.com/en-us/library/ms190267(v=sql.105).aspx
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2014-10-13 : 11:38:26
Thanks James. Works great. Less filling.

Sam
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-13 : 11:49:07
If the UDF providing the default value requires only constant value(s), you can just make the default the UDF itself.
Go to Top of Page
   

- Advertisement -