SitePoint Sponsor

User Tag List

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

    Trigger billing with Precision and scale

    hello,
    i implemented a billing system with a trigger. And the credits table with a numeric data type of precision and scale 11,2

    As follows


    Code:
    delcare @results as numeric(11,2) , @total_credits numeric(11,2)
    select @results = 
    case when receipient like '234804%' or receipient like '1%' or receipient like '44%'
    then 1.5
    else 1
    end
    from inserted
    
    begin
    
    update sms_credits set credits = (@total_credits -  @results) where username = @user
    
    
    end
    it basically allows user to send sms messages from our company website to phone numbers, however if they send phone numbers to any of the above formats 1 (USA), 44 (UK) and 234804 (MTEL- Nigeria) they get billed 1.5 credits, all other countries are 1 credit

    PROBLEM

    When any of the 1.5 credits is executed, it works well, The same goes for 1 credits.

    HOWEVER...
    Whenever a user is billed 1.5 credits and he sends to any other network that uses 1 credits, he is billed 1.5 credits at first then the billing returns to normal.

    What did i do wrong here ?

    thanks
    Afrika

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you are declaring @total_credits but you are not setting its value anywhere
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry I was trying to edit my code to only relevant data for this forum

    select @total_credits = (select credits from sms_credits where username = @user )

    However am trying something totally different, i havent tested it yet though. Its here

    Update c set
    credits = credits - case when receipient like '234804%' or receipient like '1%' or receipient like '44%'
    then 1.5
    else 1
    end
    From sms_credits c
    JOIN inserted i
    ON i.username = c.username

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just ran the code now
    its working well,

    Cheersssssssssssssssssssssssss
    Afrika


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
  •