trigger to prevent duplicate phone number??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

trigger to prevent duplicate phone number???

Author

Topic

shantanu88d
Starting Member

35 Posts

Posted - 2011-04-26 : 06:55:53

Hi,
I want to create a trigger which prevents entering of duplicate phone number. Now you would say, apply constraint itself. But design of our software requires that phone number be entered as blank for first time. So i wish to create a trigger. In sql server there is no option of BEFORE trigger. AFTER would be wrong and INSTEAD OF does not allow anything to happen. So what to do ???

Please help !

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-26 : 07:11:56

>> In sql server there is no option of BEFORE trigger
>> INSTEAD OF does not allow anything to happen

An instead of trigger replaces a before trigger. You have to complete the update in the trigger.

I suspect you would be happy with an after trigger though.

create trigger xxxx on tbl
as
if exists (select phoneno from tbl where phoneno <> '' group by phoneno having count(*) > 1)
begin
raiserror ('duplicate phone number', 16, -1)
rolback tran
end

You could limit the check using the inserted table for efficiency if you wish.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shantanu88d
Starting Member

35 Posts

Posted - 2011-04-27 : 00:50:55

This trick is great. I have to count the occurences of a simillar number! Earlier I was just finding whether a record with entered phone number existed. So was entry is first made and then trigger is executed, entry would always be there. That way, the trigger always prevented every entry, including the unique one. But in the case of trick u suggested, IT WORKS !! :)
Thanks a million !

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-27 : 00:55:28

great so what are your plans now?

The user has inserted all the appropriate data except for the phone

What do you tell them next?

Most apps would say:

"PHONE is required"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

shantanu88d
Starting Member

35 Posts

Posted - 2011-04-27 : 01:11:18

@X002548: Hmmm, nice doubt. Our application requires that a user is alocated a mobile handset first. He may or may not register his phone number in system. If he wishes to do so, in allocation his alocated mobile handset and mobile number both will show

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-27 : 13:05:20

You might be able to take advantage of a unique constraint on a computed column. For example:

CREATE TABLE #PhoneTest 
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    PhoneNumber VARCHAR(20) NULL,
    CheckPhone AS COALESCE(PhoneNumber, CAST(ID * -1 AS VARCHAR(20))) PERSISTED
)

ALTER TABLE #PhoneTest
    ADD CONSTRAINT UC_CheckPhone UNIQUE (CheckPhone DESC)

INSERT #PhoneTest (PhoneNumber)
VALUES ('123-456-7890'), (NULL), ('098-765-4321')

SELECT *
FROM #PhoneTest

-- Try instering an existing value -- Fails
INSERT #PhoneTest (PhoneNumber)
VALUES ('123-456-7890')

-- Try updating to an existing value -- Fails
UPDATE #PhoneTest
SET PhoneNumber = '123-456-7890'
WHERE PhoneNumber = '098-765-4321'

-- Try inserting a new NULL phone number
INSERT #PhoneTest (PhoneNumber) VALUES (NULL)

SELECT * FROM #PhoneTest

-- Clean up
DROP TABLE #PhoneTest

eralper
Yak Posting Veteran

66 Posts

Posted - 2011-04-28 : 03:34:29

Perhaps you can use a unique constraint instead of a trigger?

-------------
Eralper
http://www.kodyaz.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-28 : 03:45:55

On SQL 2008 you can simply use a unique filtered index. Using Lamprey's example

CREATE TABLE #PhoneTest (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    PhoneNumber VARCHAR(20) NULL,
)

CREATE UNIQUE INDEX idx_UniquePhoneNumbers ON #PhoneTest (PhoneNumber)
WHERE PhoneNumber IS NOT NULL AND PhoneNumber != ''

No computed columns, no triggers necessary

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-28 : 21:31:46

quote:
Originally posted by GilaMonster

On SQL 2008 you can simply use a unique filtered index. Using Lamprey's example

CREATE TABLE #PhoneTest (
ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
PhoneNumber VARCHAR(20) NULL,
)

CREATE UNIQUE INDEX idx_UniquePhoneNumbers ON #PhoneTest (PhoneNumber)
WHERE PhoneNumber IS NOT NULL AND PhoneNumber != ''

No computed columns, no triggers necessary

--
Gail Shaw
SQL Server MVP

WHAT???? Finally????

http://weblogs.sqlteam.com/brettk/archive/2005/04/20/4592.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-04-29 : 19:28:34

Brilliant! That's going into the toolbox!

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources