将列默认值绑定到 SQL 2005 中的函数 [英] Bind a column default value to a function in SQL 2005
问题描述
我有一列包含可由用户排序的项目:
I have a column containing items that can be sorted by the user:
DOC_ID DOC_Order DOC_Name
1 1 aaa
2 3 bbb
3 2 ccc
我正在尝试找出一种在创建条目时正确初始化 DOC_Order 的方法.一个好的值要么是相应的 DO-CID(因为它是自动分配的),要么是 MAX(DOC-ORDER) + 1
I'm trying to figure out a way to properly initialize DOC_Order when the entry is created. A good value would either be the corresponding DO-CID (since it is autoassigned), or MAX(DOC-ORDER) + 1
经过一番谷歌搜索后,我发现可以将标量函数的返回值分配给默认列.
After a bit of googling I saw it was possible to assign a scalar function's return to the default column.
CREATE FUNCTION [dbo].[NEWDOC_Order]
(
)
RETURNS int
AS
BEGIN
RETURN (SELECT MAX(DOC_ORDER) + 1 FROM DOC_Documents)
END
但我每次使用 MS SQL 管理工作室的尝试都以验证‘DOC_Order’列的默认值时出错"消息告终.
But each of my tries using MS SQL Management studio ended in a "Error validating the default for column 'DOC_Order'" message.
知道将函数分配给 DEFAULT 的确切 SQL 语法是什么吗?
Any idea of what the exact SQL syntax to assign a function to DEFAULT is?
推荐答案
像这样添加默认值的语法是
The syntax to add a default like that would be
alter table DOC_Order
add constraint
df_DOC_Order
default([dbo].[NEWDOC_Order]())
for DOC_Order
另外,当 DOC_Order 为 null 时,您可能希望更改您的函数以进行处理
Also, you might want to alter your function to handle when DOC_Order is null
Create FUNCTION [dbo].[NEWDOC_Order]
(
)
RETURNS int
AS
BEGIN
RETURN (SELECT ISNULL(MAX(DOC_ORDER),0) + 1 FROM DOC_Documents)
END
这篇关于将列默认值绑定到 SQL 2005 中的函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!