Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
clinton_eg
Yak Posting Veteran
60 Posts |
Posted - 2012-11-01 : 12:36:27
|
Hello TeamI am working on SQL Server 2008.I have setup a table to feed the Headcount details for my reports.in this table i have 3 main fields which i would like to focus on:1. Phone_Login_ID - In this field i will update the phone id that the analyst uses to login to the phone and take calls.2. Emp_ID - The ID assigned during employment3. Analyst_Status - In this field i will updated the status as "Active" if the analyst is with the company else "Inactive"Scenario - The Phone_Login_ID will get reassigned to a new joinee once an Employee leaves the company.So lets say that my table will be like below.Phone_Login_ID Emp_ID Status43512 100 Inactive43512 200 ActiveSo if by mistake i try to make the following insertINSERT INTO hc_table (Phone_Login_ID, Emp_ID, Status)VALUES (43512, 300, Active)My requirement is:it should through an error as there is already an Active user using 43512 (Phone ID)How can i go about doing this?Ewan Gilby |
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2012-11-01 : 13:03:57
|
Check out the merge statement in books online. That would be the cleanest way to do it.Mike"oh, that monkey is going to pay" |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-01 : 13:13:06
|
If you are on SQL 2008 or later, create a unique filtered index like this:CREATE UNIQUE NONCLUSTERED INDEX IDX_OnlyOneActive ON dbo.YourTableName (Phone_Login_ID, Status) WHERE Status = 'Active' ; |
|
|
clinton_eg
Yak Posting Veteran
60 Posts |
Posted - 2012-11-05 : 00:06:34
|
Thanks for the suggestions.. it helped :)Ewan Gilby |
|
|
|
|
|