Trigger? Constraint? Computed column?

[zcumbag]

Hi.

I was wondering how I should go about doing this thing. I need to put a value in a column that is based on values on other columns in the same row.

When this statement executes I need to put a value in Col3.

insert into myTable(Col1, Col2)
values(25, -14)

Something like so:

if(Col1 >0 AND Col2 <0)
set Col3 = Col1 - Col2
else
set Col3 = Col1;

I don't now quite how to solve this. I am really going to need this value in a column. Calculating the value at retrieval is not on option...

I appreciate all help. I'm using SQL Server 2005.

Thanks!

[citrus]

On the basis that the data in Col1 and/or Col2 might change after the original insert, I reckon your best solution is to use a computed column. The following Transact-SQL will create a table called myTable with the columns you want.

CREATE TABLE myTable(
[Col1] [int] NOT NULL,
[Col2] [int] NOT NULL,
[Col3] AS (case
when [Col1]>(0) AND [Col2]<(0) then [Col1]-[Col2]
else [Col1] end) PERSISTED
)

Play around with the case statement if the required expressions don't exactly match your requirements.

The PERSISTED keyword is required in order to materialise the column data. If left out, the column data is only computed when queried.

A trigger would also do the job but is probably overkill in this scenario.

Hope that helps!

[Oscarj_col]

On the basis that the data in Col1 and/or Col2 might change after the original insert, I reckon your best solution is to use a computed column. The following Transact-SQL will create a table called myTable with the columns you want.

CREATE TABLE myTable(
[Col1] [int] NOT NULL,
[Col2] [int] NOT NULL,
[Col3] AS (case
when [Col1]>(0) AND [Col2]<(0) then [Col1]-[Col2]
else [Col1] end) PERSISTED
)

Play around with the case statement if the required expressions don't exactly match your requirements.

The PERSISTED keyword is required in order to materialise the column data. If left out, the column data is only computed when queried.

A trigger would also do the job but is probably overkill in this scenario.

Hope that helps!

I like this solution, and i think a trigger isn't necessary.