Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: creating a trigger

    I have a city table and customer table. When I insert a customer record I want a trigger which check the customer city value is one the value in city table.

    I tried like this but it didn't work

    CREATE OR REPLACE TRIGGER city_check

    BEFORE INSERT on customer

    BEGIN

    IF :new.ccity NOT IN

    (select CCityName

    FROM CCities)

    THEN RAISE_APPLICATION_ERROR(-20500, ‘The customer city must be in the ccities table.’);

    END IF:

    END;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >it didn't work
    Above is not a valid Oracle error message.
    My car does not work.
    Tell me how to make my car go, please.

    Code:
    CREATE OR REPLACE TRIGGER city_check 
      BEFORE INSERT ON customer 
    BEGIN 
      IF :new.ccity NOT IN (SELECT ccityname 
                            FROM   ccities) THEN 
        Raise_application_error(-20500,'The customer city must be in the ccities table.'); 
      END IF; 
    END;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You don't need a trigger for this.

    The correct way of doing this, is to create a foreign key between customer and CCities.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Apart from the fact that @shammat is absolutely right, here you are: you can not use a subquery in an IF statement. Basically, you'd need to first select a value into a local variable and then use this variable in an IF-THEN-ELSE. It would look like this:
    Code:
    SELECT some_value INTO local_variable
      FROM some_table
      WHERE some_condition;
    
    IF local_variable = another_something THEN
       ...
    END IF;
    However, as such a principle promises to fail if there's no record found, IF wouldn't even catch that as the error would/should be handled in an exception handling section of your PL/SQL procedure (which a trigger, actually, is).

    Therefore, your code might look like this:
    Code:
    create or replace trigger city_check
      before insert on customer
      for each row
    declare
      l_city_name ccities.ccityname%type;
    begin
      select ccityname into l_city_name
        from ccities
        where ccityname = :new.ccityname;
    exception
      when no_data_found then
        raise_application_error(-20500, 'The customer city must be in the ccities table');
    end;
    In other words: if a record is found(*), do nothing. Otherwise, raise an error.

    (*) I suppose that there can not be two cities with the same name in your table - I *know* that this is possible in real world, but - in that case - you'd need to use another identifier along with city name; otherwise, you'll need to handle TOO_MANY_ROWS as well.

  5. #5
    Join Date
    Apr 2009
    Posts
    2

    Thank you very much

    Thank you for replaying to my post. I tested and it works fine.

Posting Permissions

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