SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Update Trigger

  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update Trigger

    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


    Code:
    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 =

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your datatype of @phone is INT, the LIKE operator does not work on numbers.

    Try using
    if @phone > 079999999 BEGIN

  3. #3
    SitePoint Guru puco's Avatar
    Join Date
    Feb 2005
    Location
    Slovakia
    Posts
    785
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

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

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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%'


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •