CODEWITHSUNDEEP
sql-server
MohammadSia
MohammadSia

Reputation: 37

How to update sql table column value dynamicllay based on another value?

I have one table with the following structure and some values :

PK_ID      Webinar_Name              Start_Date                         Is_Active

1                          w1                          3/1/2016                          True

2                          w2                          1/7/2016                          True

3                          w3                          4/9/2016                          True

Now i want the Is_Active column value to be updated dynamically (i.e : NOT after update,insert and delete) based on the date.. if its matches the current date so the Is_Active column value will set to be false.

I've tried to use triggers but it should be after some action like insert and update which is conflicts with my requirements.

I appreciate any help . thanks

Upvotes: 0

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use a computed column:

alter table t add Is_Active as (case when cast(start_date as date) = cast(getdate() as date) then 0 else 1 end)

Of course, you might need to remove the column first.

Note: this uses 1 for true and 0 for false. These constants are not defined in SQL Server. You can use whatever values you like.

Upvotes: 2

cf_en
cf_en

Reputation: 1661

It looks like you should be able to use a computed column for this, though I've never tried it.

https://msdn.microsoft.com/en-GB/library/ms188300.aspx

I guess it'd look something like this:

ALTER TABLE webinars ADD ActiveFlag AS
    CASE WHEN Start_Date = CAST(GetDate() AS date) THEN 'True'
    ELSE 'False' END

Upvotes: 3

Related Questions