Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2014
    Posts
    4

    Unanswered: Hello

    Hello,

    I am having a difficulty with this trigger,

    its purpose is to check the first name and the last name when inserting in owner table and print a msg if found and stop the insertion else it just let it,

    here
    ------
    create or replace TRIGGER search_for_duplication
    before INSERT ON owner
    FOR EACH ROW

    declare
    v_min owner.owner_id%type;
    v_max owner.owner_id%type;
    fname owner.owner_first_name%type;
    lname owner.owner_last_name%type;
    counter number;
    BEGIN
    select max(owner_id), min(owner_id), owner_first_name, owner_last_name into v_max, v_min, fname, lname
    from owner;
    counter := v_min;
    LOOP
    if :new.owner_first_name != fname and :new.owner_last_name != lname
    then
    counter := (counter + 1);
    else
    dbms_output.put_line('user exisit');

    end if;
    exit when (counter > v_max);
    END LOOP;

    END;


    HELP PLEASE!

  2. #2
    Join Date
    Apr 2014
    Posts
    4

    Trigger issue, catch duplication before insert

    Hello guys,
    I am trying to create a trigger that searches for the first and last name for the owner table when trying to insert into it, and if it already exist it should print a message says that and it should stop the insert process, otherwise it does nothing,
    my code is not working for some reasons, i need your help,
    thanks
    Code:
    create or replace TRIGGER search_for_duplication 
    before INSERT ON owner
    FOR EACH ROW 
    
    declare 
    v_min owner.owner_id%type;
    v_max owner.owner_id%type;
    fname owner.owner_first_name%type;
    lname owner.owner_last_name%type;
    counter number;
    BEGIN
    select max(owner_id), min(owner_id), owner_first_name, owner_last_name into v_max, v_min, fname, lname 
    from owner;
    counter := v_min;
    LOOP
    if :new.owner_first_name != fname and :new.owner_last_name != lname
    then
     counter := (counter + 1);
    else
     dbms_output.put_line('user exisit');
    
    end if;
    exit when (counter > v_max);
    END LOOP;
    
    END;

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE UNIQUE INDEX XAK01_owner ON owner (owner_last_name, owner_first_name);
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2014
    Posts
    4
    yeah thanks alot, but is it possible to be a trigger somehow? =\

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Possible, certainly. Waste of time/energy/resources, definitely.

    This index will perform better than a trigger (since the trigger would either need to use this index or worse yet scan for an offending row). The trigger is more difficult to code (you've already proved that). The trigger will also needlessly waste resources while making the user wait for an answer.

    This is a great example of something that you could do, but you shouldn't do it!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Apr 2014
    Posts
    4
    okay thanks alot,

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >my code is not working for some reasons,

    see a picture of my car which not working for some reason.
    Tell me how to make my car go.

    since we don't have your tables or data, we can not run, test or debug posted code.
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by anacedent View Post
    since we don't have your tables or data, we can not run, test or debug posted code.
    Good point, but we CAN replace it with simpler (declarative) code that meets all of the stated criteria. Faster, easier, standard, no debugging needed... What's not to like about that solution???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The other problem is that a trigger can only stop an insert by generating an error, not by exiting also you can't query in a trigger the table that the trigger is on if it is "on each row". The other problem is that your entire idea is false. stopping an insert on the same name is a bad idea. Any organization of any size will have duplicate names like John Smith. Much better to put a unique index on something like Social Security number that is unique to each person.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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