Default constraint to automatically change a value

Question

I have a table ProviderValueCard. Is it possible to set up a default constraint to when a new row is created, if column SessionsProgress is > 0 then automatically set the value of the entry for column SurveyCompleted = 1 and set it = 0 if SessionProgress = 0?

So psedo: If SessionsProgress > 0, then SurveyCompleted = 1 else SurveyCompleted = 0

I got the idea from marc_s answer on this question: Automatically inserting datetime with insert

Check it out http://stackoverflow.com/questions/15521546/how-to-create-default-constraint-dependent-other-column-in-sql-server — abatishchev, Oct 03 '14 at 17:01
@abatishchev - You beat me to it.. you could also use a trigger for this, but computed seems to be the way to go. — Dave C, Oct 03 '14 at 17:02
If it doesn't work, you need a trigger (comments are allows only 1 per 15 seconds) (: — abatishchev, Oct 03 '14 at 17:03
For the trigger :) http://msdn.microsoft.com/en-us/library/ms189799.aspx — Pred, Oct 03 '14 at 17:03
Awesome find. What is the syntax though if I want a ">" when using it here, I get a syntax error on the >: `ALTER TABLE ProviderValueCard ADD SurveyCompleted AS CASE [SessionsProgress] WHEN > '0' THEN 1 WHEN '0' THEN 0 END` — user3749447, Oct 03 '14 at 17:11

score 0 · Accepted Answer · answered Oct 03 '14 at 17:25

0

Thanks to abatishchev, here's what worked. I couldn't figure out the > syntax error :/. I didn't have anything in that column that mattered yet, so dropping it wasn't an issue.

    ALTER TABLE ProviderValueCard DROP COLUMN SurveyCompleted

ALTER TABLE ProviderValueCard ADD SurveyCompleted AS CASE [SessionsProgress] WHEN '1' THEN 1
WHEN '2' THEN 1
WHEN '3' THEN 1
WHEN '4' THEN 1
WHEN '5' THEN 1
WHEN '0' THEN 0
END

answered Oct 03 '14 at 17:25

user3749447

299
2
5
13

Couldn't you just say `WHEN '0' THEN '0' ELSE '1' END`? – AHiggins Oct 03 '14 at 17:46
1

And when they enter as 6? – paparazzo Oct 03 '14 at 21:44

score 0 · Answer 2 · answered Oct 03 '14 at 20:43

0

You get that behavior with bit

  declare @bit bit;
  set @bit = 0;
  print @bit;
  set @bit = 1;
  print @bit;
  set @bit = 2;
  print @bit;
  set @bit = 2000;
  print @bit;

answered Oct 03 '14 at 20:43

paparazzo

44,497
23
105
176

Default constraint to automatically change a value

2 Answers2