Make a column receives 0 or 1 based on the value of another column

Question

I want to know if it's possible to create a column in a table, that get's their value automatically based on the value of another column in the same table, example below to clarify:

CREATE TABLE dbo.example
(
    m_id INT NOT NULL CONSTRAINT PK_mid PRIMARY KEY IDENTITY (1,1),
    m_name NVARCHAR(30) NOT NULL,
    m_startdate DATE NOT NULL CONSTRAINT CHK_startdate CHECK(m_startdate <= SYSDATETIME()),
    m_enddate DATE CONSTRAINT CHK_enddate CHECK(m_enddate <= SYSDATETIME()),
    m_status INT CONSTRAINT CHK_status CHECK(m_status = 0 or m_status = 1)
)

I want to make m_status receive 0 if m_enddate is null and 1 if it's not null. This of course would be upon an insert of a row.

As far as I'm aware of - you can't do that. What you can do is a trigger perhaps . — sagi, Nov 09 '16 at 14:05
please refer this one [link](http://stackoverflow.com/questions/3438066/check-constraint-on-multiple-columns) — Husen, Nov 09 '16 at 14:07
Indeed, the only way is through a trigger (by the way, not only upon INSERT but also upon UPDATE). — FDavidov, Nov 09 '16 at 14:07
Possible duplicate of [Set the default value of a column based on another column of a different data type](http://stackoverflow.com/questions/26536108/set-the-default-value-of-a-column-based-on-another-column-of-a-different-data-ty) — sagi, Nov 09 '16 at 14:08

Michael buller · Answer 1 · 2016-11-09T19:16:33.487

5

You could use a calculated column as follows...

 CREATE TABLE dbo.example
(
m_id INT NOT NULL CONSTRAINT PK_mid PRIMARY KEY IDENTITY (1,1),
m_name NVARCHAR(30) NOT NULL,
m_startdate DATE NOT NULL CONSTRAINT CHK_startdate CHECK(m_startdate <= SYSDATETIME()),
m_enddate DATE CONSTRAINT CHK_enddate CHECK(m_enddate <= SYSDATETIME()),
m_status AS CASE
    WHEN m_enddate is null THEN 0 ELSE 1 
    END 
)

edited Nov 09 '16 at 19:16

answered Nov 09 '16 at 14:14

Michael buller

566
4
11

GuidoG · Answer 2 · 2016-11-09T15:22:04.863

A calculated column as Michael suggested is best, but in case that you cannot alter the table then you could make a view for this.

CREATE VIEW dbo.vwExample AS
  SELECT m_id,
         m_name,
         m_startdate,
         m_enddate,
         CASE WHEN m_enddate is null THEN 0 ELSE 1 END as m_status
  FROM   dbo.example

Now you can do

 select * from dbo.vwExample

and it will have the correct value for m_status without having to alter the table itself.

score 1 · Answer 3 · answered Nov 10 '16 at 22:34

In T-SQL ,you could use Instead of insert/update triggers, which allow you to intercept the inserts and updates and inject logic.

CREATE TRIGGER dbo.exampleInsertTrigger
   ON   [dbo].[example]
   INSTEAD OF INSERT
AS 
BEGIN
INSERT INTO [dbo].[example]
    ([m_name]
    ,[m_startdate]
    ,[m_enddate]
    ,[m_status])
SELECT
    [m_name]
    ,[m_startdate]
    ,[m_enddate]
    ,CASE
    WHEN [m_enddate] is null THEN 0 ELSE 1 
    END
FROM inserted
END


CREATE TRIGGER dbo.exampleUpdateTrigger
   ON   dbo.example
   INSTEAD OF UPDATE
AS 
BEGIN
UPDATE [dbo].[example]
    SET
    [m_name] = inserted.[m_name]
    ,[m_startdate] = inserted.[m_startdate]
    ,[m_enddate] = inserted.[m_enddate]
    ,[m_status] = CASE
    WHEN inserted.[m_enddate] is null THEN 0 ELSE 1 
    END
FROM inserted
WHERE inserted.[m_id] = [dbo].[example].[m_id]
END

Make a column receives 0 or 1 based on the value of another column

3 Answers3