August 22, 2008 at 4:24 am
Hi there,
I'm trying to update a column (TypeID) whenever another column (Type) is being inserted. I wish to do this with a constraint on TypeID, not with a trigger (if that's possible).
This is an example of the table:
--Create test table.--
CREATE TABLE tbTest
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Type] [char](1) NOT NULL,
[TypeID] [int]
)
To fill the table:
--Fill test table.--
INSERT INTO tbTest (Type)
SELECT'Y' UNION ALL
SELECT'S' UNION ALL
SELECT'D'
Now here comes the constraint as I would like it (it doesn't work, that's why I'm posting):
ALTER TABLE tbTest
ADD CONSTRAINT fkc_MessageTypeID
CHECK
(
WHEN (Type = 'Y') THEN TypeID = '33'
WHEN (Type = 'S') THEN TypeID = '37'
WHEN (Type = 'D') THEN TypeID = '40'
ELSE '33'
)
So, is this possible at all? And if so, what should I do?
Thanks in advance for helping me out!
August 22, 2008 at 4:47 am
You need to use a computed column for what you want, not a constraint:-
CREATE TABLE tbTest
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Type] [char](1) NOT NULL,
[TypeID] as (CASE WHEN Type='Y' THEN '33'
WHEN Type='S' THEN '37'
WHEN Type='D' THEN '40'
ELSE '33' END)
)
August 22, 2008 at 4:51 am
Thanks a lot Ian! That's exactly what I needed 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply