SQL 如何根据另一列为SQL中的一列设置默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/711159/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set a default value for one column in SQL based on another column
提问by Dynamo
I'm working with an old SQL 2000 database and I don't have a whole lot of SQL experience under my belt. When a new row is added to one of my tables I need to assign a default time value based off of a column for work category.
我正在使用旧的 SQL 2000 数据库，但我没有很多 SQL 经验。当一个新行添加到我的一个表中时，我需要根据工作类别的列分配一个默认时间值。
For example, work category A would assign a time value of 1 hour, category B would be 2 hours, etc...
例如，工作类别 A 将分配 1 小时的时间值，类别 B 将分配 2 小时，等等......
It should only set the value if the user does not manually enter the time it took them to do the work. I thought about doing this with a default constraint but I don't think that will work if the default value has a dependency.
如果用户没有手动输入他们完成工作所花费的时间，它应该只设置该值。我考虑过使用默认约束来执行此操作，但如果默认值具有依赖性，我认为这不会起作用。
What would be the best way to do this?
什么是最好的方法来做到这一点？
采纳答案by Stephen Wrighton
I would use a trigger on Insert.
我会在插入时使用触发器。
Just check to see if a value has been assigned, and if not, go grab the correct one and use it.
只需检查是否已分配值，如果没有，请获取正确的值并使用它。
回答by Petrus Theron
Use a trigger as suggested by Stephen Wrighton:
使用 Stephen Wrighton 建议的触发器：
CREATE TRIGGER [myTable_TriggerName] ON dbo.myTable FOR INSERT
AS
SET NOCOUNT ON
UPDATE myTable
SET
timeValue = '2 hours' -- assuming string values
where ID in (
select ID
from INSERTED
where
timeValue = ''
AND workCategory = 'A'
)
回答by HLGEM
Be sure to write the trigger so it will handle multi-row inserts. Do not process one row at a time in a trigger or assume only one row will be in the inserted table.
请务必编写触发器，以便它处理多行插入。不要在触发器中一次处理一行，也不要假设插入的表中只有一行。
回答by Sergiu
If what you are looking for is to define a column definition based on another column you can do something like this:
如果您要查找的是基于另一列定义列定义，您可以执行以下操作：
create table testable
(
c1 int,
c2 datetime default getdate(),
c3 as year(c2)
);
insert into testable (c1) select 1
select * from testable;
Your result set should look like this :
您的结果集应如下所示：
c1 | c2 | c3
1 | 2013-04-03 17:18:43.897 | 2013
As you can see AS (in the column definition) does the trick ;) Hope it helped.
正如您所看到的，AS（在列定义中）可以解决问题；) 希望它有所帮助。
回答by user3371965
I can think of two ways:
我可以想到两种方法：
- triggers
- default value or binding (this should work with a dependency)
- 触发器
- 默认值或绑定（这应该与依赖项一起使用）
Triggers seem well explained here, so I won't elaborate. But generally I try and stay away from triggers for this sort of stuff, as they are more appropriate for other tasks
触发器似乎在这里解释得很好，所以我不会详细说明。但通常我会尽量远离这类东西的触发器，因为它们更适合其他任务
"default value or binding" can be achieved by creating a function e.g.
“默认值或绑定”可以通过创建一个函数来实现，例如
CREATE FUNCTION [dbo].[ComponentContractor_SortOrder] ()
RETURNS float
AS
BEGIN
RETURN (SELECT MAX(SortOrder) + 5 FROM [dbo].[tblTender_ComponentContractor])
END
And then setting the "default value or binding" for that column to ([dbo].ComponentContractor_SortOrder)
然后将该列的“默认值或绑定”设置为 ([dbo].ComponentContractor_SortOrder)
回答by IDisposable
So, for example, in a TAG table (where tags are applied to posts) if you want to count one tag as another...but default to counting new tags as themselves, you would have a trigger like this:
因此，例如，在 TAG 表（标签应用于帖子）中，如果您想将一个标签视为另一个标签……但默认情况下将新标签视为自己，您将有一个这样的触发器：
CREATE TRIGGER [dbo].[TR_Tag_Insert]
ON [dbo].[Tag]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.Tag
SET [CountAs] = I.[ID]
FROM INSERTED AS I
WHERE I.[CountAs] IS NULL
AND dbo.Tag.ID = I.ID
END
回答by tpdi
Yeah, trigger.
是的，触发器。
Naturally, instead of hard-coding the defaults, you'll look them up from a table.
自然地，您无需对默认值进行硬编码，而是从表中查找它们。
Expanding on this, your new table then becomes the work_category table (id, name, default_hours), and you original table maintains a foreign key to it, transforming fom (id, work_category, hours) to (id, work_category_id, hours).
对此进行扩展，您的新表然后成为 work_category 表（id，name，default_hours），并且您的原始表维护它的外键，将 fom (id, work_category, hours) 转换为 (id, work_category_id, hours)。
回答by Learning
Generally I steer away from triggers. Almost all dbms have some sort of support for constraints.
通常我会避开触发器。几乎所有的 dbms 都对约束有某种支持。
I find them easier to understand , debug and maintain.
我发现它们更易于理解、调试和维护。
