Update Trigger

[afrika]

hello,
I have a website offering web2phone sms services. and I have a table which users register on my website. However i would like to keep track of certain phone numbers they enter.

Knowing that international formats of my country starts with 234 and phone numbers start with 080 i would like to search for any entry starting with 080 and update it with a trigger on insert to 234

how do i get this done efficiently, cos am stuck

thanks
Afrika

declare @phone int , @phone2 int

select @phone = (select phone from inserted)
select @phone2 = (select phone from inserted)--Am trying to make this update the phone with the international format

if @phone like 080%
begin
update users
set phone =

[asterix]

Your datatype of @phone is INT, the LIKE operator does not work on numbers.

Try using
if @phone > 079999999 BEGIN

[puco]

Ehm, you are storing phone number as int? How do you distinguish betwwen number 0801222 and 801222. I always thought that phone numbers are string values. As asterix said you can't use like on numeric datatype. You can covert it to varchar using cast or convert or you can determine the two most significant digits (80 in your case, since the leading zero would be eliminated) using some math (number / 10^(ceil (log10 (number)) - 1) or something like this.

The other possibility is to format the number in your web site's business logic.

To the trigger you wanted:

update inserted set phone = ... where charindex ('80', cast (phone as char (10))) = 1

[asterix]

puco:

He needs to have the numbers as numeric data because they are being fed into a text messaging gateway - which only accept numbers not text data.

[afrika]

hey !
Thanks all

I finally got the code sorted out. But thanks for your advice, asterix and puco

update users
set phone = '234' + u.phone
from users u
join inserted i
on u.pk = i.pk
where u.phone like '080%'