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.
| 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 |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2014-10-13 : 11:38:26
|
| Thanks James. Works great. Less filling.Sam |
|
|
|
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. |
|
|
|
|
|
|
Computed column question