Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: Oracle trigger to flag a name from one table that is entered in another

    Good day,

    I need to build a trigger that fires when a name is entered into a member table but will compare the name to one in another table to see if there is a match and will display a message that a match was found.

    can you plese help me.

    CREATE TRIGGER NAME_MATCH
    AFTER INSERT OR UPDATE ON MEMBER
    FOR EACH ROW
    BEGIN
    IF INSERTING OR UPDATING THEN
    WHERE LIST.LISTNAME=MEMBER.MEMBERNAME
    OUTPUT MESSAGE_ALERT ('NAME MATCH FOUND);
    ELSE
    OUTPUT MESSAGE_ALERT ('NO MATCH FOUND');
    END NAME_MATCH;

    thank you

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please realize that we don't have your tables & we don't have your data.
    Therefore we can't run, test or improve your posted SQL.

    How does TRIGGER know anything about "LIST.LISTNAME"?
    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 2006
    Posts
    82
    Do you mean something like that
    Code:
    create or replace trigger child_test 
    after insert or update on child
    for each row
    declare
    col_value int:=0;
    begin
    select 1 into col_value from parent where i=:NEW.j and rownum<2;
    dbms_output.put_line('Found');
    EXCEPTION 
           WHEN NO_DATA_FOUND THEN
    			     dbms_output.put_line('Not found'); 			 
    end;
    Please be aware that such solution may cause poor database performance if tables are big or there are many inserts/updates operations.

  4. #4
    Join Date
    Jul 2011
    Posts
    2
    thank you RSKI i shall try that approach

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DBMS_OUTPUT will be visible in a very, very rare cases. Who runs the application via SQL*Plus calls (or a client that is capable of displaying DBMS_OUTPUT)?

    Perhaps you'd rather enforce referential integrity constraint?

Posting Permissions

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