-
Trigger? Constraint? Computed column?
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!
-
Try using a Computed Column
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!
-
Originally Posted by 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!
I like this solution, and i think a trigger isn't necessary.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|