none
Set default value for a column, depending on another column in the same table.

    Question

  • Is it possible to Set default value for a column, depending on another column in the same table.

    If not, Is there any other constraint, that could do something similar.

    I know, this can be done by a before insert trigger. I was just wondering , if there is a easier way
    Thursday, December 10, 2009 9:50 PM

Answers

  • You can modify the trigger to update if the value is not supplied only, as follows. I'm not sure there is any other way to do it, or easier, sorry.

    --Create Table
    CREATE TABLE t (ID INT,Due_date DATETIME, Internal_Due_Date DATETIME)
    
    --Create Trigger
    CREATE TRIGGER Tr_Update
    ON t
    AFTER INSERT,UPDATE
    AS
    
    UPDATE t SET Internal_Due_Date = DATEADD(dd,-7,i.Due_Date)
    FROM inserted i
    JOIN t ON i.ID = t.ID
    WHERE i.Internal_Due_Date IS NULL
    
    --Insert data and test it
    INSERT INTO t (id,due_date,Internal_Due_Date)
    SELECT 1,'12/10/2009','12/09/2009' UNION ALL
    SELECT 2,'10/10/2009',NULL
    
    SELECT * FROM t
    


    Abdallah, PMP, ITIL, MCTS
    Thursday, December 10, 2009 11:12 PM

All replies

  • Have you looked at the CHECK constraint? Take a look at this link

    http://msdn.microsoft.com/en-us/library/ms188258.aspx
    Abdallah, PMP, ITIL, MCTS
    Thursday, December 10, 2009 9:53 PM
  • Hello Abdallah,

    Thank you for your time.
    Yes, I do know about check constraint, and I did try it on my table, but the moment , i refer another column name, i get the error, error in validating the constraint.

    What I am trying to achieve is, I have a table with 2 flds.

    Due_date and Internal_Due_Date.

    I want Internal_Due_Date to be a week before the actual_due_date by default.
    Thursday, December 10, 2009 10:00 PM
  • Do you have NULL dates in Actual_Due_Date?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, December 10, 2009 10:25 PM
  • No.

    Probably, i am not using the right expression. what would be the expression in the above case.

    i tried

    DATEADD(DAY, -7, cast(due_date as DateTime)),

    due_date-7 etc.
    Thursday, December 10, 2009 10:38 PM
  • You can't use expressions with the DEFAULT constraint. I don't see a reason to save that data in your table since you can easily use the DATEADD function to get the result. However, if you insist and you want it to be consistent all the time, I would use a trigger as follows.

    --Create Table
    CREATE TABLE t (ID INT,Due_date DATETIME, Internal_Due_Date DATETIME)
    
    --Create Trigger
    CREATE TRIGGER Tr_Update
    ON t
    AFTER INSERT,UPDATE
    AS
    
    UPDATE t SET Internal_Due_Date = DATEADD(dd,-7,i.Due_Date)
    FROM inserted i
    JOIN t ON i.ID = t.ID
    
    --Insert data and test it
    INSERT INTO t (id,due_date)
    SELECT 1,'12/10/2009' UNION ALL
    SELECT 2,'10/02/2008'
    
    SELECT * FROM t


    Abdallah, PMP, ITIL, MCTS
    Thursday, December 10, 2009 10:51 PM

  • Thanks abdallah.

    I need that , internal_due_date field, cause, it could be set by the user as well. it is not always a week before actual date.

    I did have the trigger in place. But was just wondering if there is any other way.

    Guess, i should go with the trigger after all.

    Thanks a ton for your time on this.

    I appreciate it.
    Thursday, December 10, 2009 10:56 PM
  • You can modify the trigger to update if the value is not supplied only, as follows. I'm not sure there is any other way to do it, or easier, sorry.

    --Create Table
    CREATE TABLE t (ID INT,Due_date DATETIME, Internal_Due_Date DATETIME)
    
    --Create Trigger
    CREATE TRIGGER Tr_Update
    ON t
    AFTER INSERT,UPDATE
    AS
    
    UPDATE t SET Internal_Due_Date = DATEADD(dd,-7,i.Due_Date)
    FROM inserted i
    JOIN t ON i.ID = t.ID
    WHERE i.Internal_Due_Date IS NULL
    
    --Insert data and test it
    INSERT INTO t (id,due_date,Internal_Due_Date)
    SELECT 1,'12/10/2009','12/09/2009' UNION ALL
    SELECT 2,'10/10/2009',NULL
    
    SELECT * FROM t
    


    Abdallah, PMP, ITIL, MCTS
    Thursday, December 10, 2009 11:12 PM
  • Thanks abdallah.
    Thursday, December 10, 2009 11:22 PM