Results 1 to 5 of 5

Thread: Trigger Help

  1. #1
    Join Date
    May 2006
    Location
    India
    Posts
    5

    Unhappy Unanswered: Trigger Help

    I have a table with 3 columns name, phone number and address. I want to create a trigger which will spit an error if a new record is inserted whose phone number is less than 10 or greater thant 10 digits

    ie only a 10 digit number should be inserted in the phone number field else an error.


    I know how to create simple triggers, wrote this one on my own but it doesnt work. (not sure if this query is valid or not)

    create function check_no () returns opaque as '
    begin
    if(len(new.tel) != 10)
    then
    RAISE EXCEPTION ''Invalid customer ID number.'';
    end if
    end;
    ' LANGUAGE 'plpgsql';

    When i execute this i get

    ERROR: syntax error at or near "end" at character 139
    LINE 7: end;

    Any help would be appreciated.

  2. #2
    Join Date
    May 2006
    Location
    India
    Posts
    5
    Problem solved, Mods can lock this thread.

    Solution

    create function check_no () returns opaque as '
    begin
    if(length(new.tel) != 10)
    then
    RAISE EXCEPTION ''Invalid customer ID number.'';
    end if;
    return new;
    end;
    ' LANGUAGE 'plpgsql';

    create trigger check1 before insert on members for each row
    execute procedure check_no();

    Works flawlessly

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Glad you got the trigger working, but wouldn't it have been easier to just add a check constraint to the field in question?

    Give it a name like Invalid_Customer_ID_Number, for instance.

    And have the constraint check be char_length(tel) != 10
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    May 2006
    Location
    India
    Posts
    5
    Quote Originally Posted by loquin
    Glad you got the trigger working, but wouldn't it have been easier to just add a check constraint to the field in question?

    Give it a name like Invalid_Customer_ID_Number, for instance.

    And have the constraint check be char_length(tel) != 10
    Yes it would have been easier but my professor wanted one trigger in my project ( a simple login system with an image gallery in php postgres)

    So i thought i will create a trigger for checking the phone number.

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Ahhh. So, YOU have a constraint against constraints.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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