Default column value based on the value of a different column

SQL Server 2005. I have a table with ColumnA bit, ColumnB int

Can I add a default value to ColumnB so that ColumnB is 15 if ColumnA is 1 and ColumnB is 0 if ColumnA is 0?

I know I could do this with a开发者_开发百科 trigger but I my boss is prejudice against triggers (he needs trigger sensitivity training).

if your ColumnB can only be 15 or zero, you can make it a computed column based on ColumnA. here is the code to add a new computed column:

ALTER TABLE YourTable ADD YourComputedColumn AS ColumnA*15
go

Have you considered a computed column instead?. It's not exactly the same thing... but perhaps it is enough for you?

The only non-trigger means I can think of is to have the logic in a stored procedure. It's still my preference compared to a trigger, because it's the most likely place to check. A trigger means you now have two places to check when errors/issues occur.

Beyond that, it depends on the usage - in the foreseeable future, could there be records that should not be affected by the trigger logic? If not, a trigger is a more centralized means of solving the issue.

I think this may be handled better with a stored procedure that you use for inserts. Also, it is hard to tell the best approach without some understanding of what sort of data the table is holding. I suspect you may be able to get a clearer model by splitting out ColumnB into another table.

Computed columns are pretty useful in certain scenarios. It is worth to mention that they can (but not need to) be persisted. The choice of whether column should be persisted or not depends on how it is used. If it is persisted it consumes space as 'regular' column, otherwise is calculated at runtime. You can also create indexes on computed columns.

http://msdn.microsoft.com/en-us/library/ms191250.aspx

other way:

alter table yourTABLe add ColumnB as case when coalesce(ColumnA,0) = 15 then 0 else 1 end
go