如何基于另一列在SQL中为一列设置默认值

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?

最好的方法是什么？

8 个解决方案

#1

I would use a trigger on Insert.

我会在Insert上使用触发器。

Just check to see if a value has been assigned, and if not, go grab the correct one and use it.

只需检查是否已分配值，如果没有，请抓住正确的值并使用它。

#2

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'
)

#3

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（在列定义中）可以解决问题;）希望它有所帮助。

#4

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.

务必写入触发器，以便处理多行插入。不要在触发器中一次处理一行，或者假设插入的表中只有一行。

#5

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）。

#6

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

#7

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.

我发现它们更容易理解，调试和维护。

#8

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）

#1