How to set a default value for one column in SQL based on another column

Question

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.

For example, work category A would assign a time value of 1 hour, category B would be 2 hours, etc...

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?

Answer 1

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.

Answer 2

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

Answer 3

Generally I steer away from triggers. Almost all dbms have some sort of support for constraints.

I find them easier to understand , debug and maintain.

Answer 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 inteh inserted table.

Answer 5

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:

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

Answer 6

Use a trigger as suggested by 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'
)