Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Unanswered: trigger on one table select field from another table

    I have a trigger on a table that on insert or update, inserts several fields into a new table and I want to include a field from another table. Like this:
    if (updating and :old.description <> :new.description) then
    insert into position_change (position, company, effect_date, department, job_code, description, locat_code, r_shift, typeofchange, change_dt, field_changed, old_value, end_date )
    values (:old.position, :old.company, :old.effect_date, :old.department, :old.job_code, :new.description, :old.locat_code, :old.r_shift, 'U', sysdate, 'description', :old.description, :old.end_date);
    end if;
    This works but I need to include an employee number from a different table. Like:
    if (updating and :old.description <> :new.description) then
    insert into position_change (position, company, employee, effect_date, department, job_code, description, locat_code, r_shift, typeofchange, change_dt, field_changed, old_value, end_date )
    values (:old.position, :old.company, (select employee from pas a, pos b where a.company = b.company and a.position = b.position), :old.effect_date, :old.department, :old.job_code, :new.description, :old.locat_code, :old.r_shift, 'U', sysdate, 'description', :old.description, :old.end_date);
    end if;

    But this has error because the select returns more than one row.
    Is there a way to get at this employee?
    Thanks very much for any ideas.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But this has error because the select returns more than one row.
    >Is there a way to get at this employee?

    It is YOUR application; not ours.
    If you don't know the answer, then nobody here will know it for you.
    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 2011
    Posts
    3

    further clarification

    Well then, can anyone tell me this,
    when using a select within a trigger like this do I need to refer to the columns with ld in front of them?
    Or do I need a subquery within this select to limit the results to get only one employee?
    I know that I join the two tables by company and position. But how do I tell it that I mean this ld.companty and ld.position, not all of them?
    And thanks for any useful advice.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Could you try something like this? Not sure it'll work, though.
    Code:
    insert into position_change 
    (position, company, employee, 
     effect_date, department, job_code, 
     description, locat_code, r_shift, 
     typeofchange, change_dt, field_changed, 
     old_value, end_date 
    )
    (select 
       :old.position, :old.company, a.employee
       :old.effect_date, :old.department, :old.job_code, 
       :new.description, :old.locat_code, :old.r_shift, 
       'U', sysdate, 'description', 
       :old.description, :old.end_date
     from pas a, pos b
     where a.company = b.company
       and a.position = b.position
       and a.position = :old.position
       and <additional conditions, if necessary>
    );

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >when using a select within a trigger like this do I need to refer to the columns with old in front of them?
    for INSERT, all "old" columns are NULL

    >Or do I need a subquery within this select to limit the results to get only one employee?
    yes

    >I know that I join the two tables by company and position. But how do I tell it that I mean this ld.companty and ld.position, not all of them?

    develop SELECT with your SQL client using hard coded values
    When you obtain SELECT that only return the correct single row,
    then modify it to work within TRIGGER 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.

  6. #6
    Join Date
    Nov 2011
    Posts
    3

    very close

    It didn't work exactly like that but I think I have it. The code below worked as far as syntax, but I can't get it tested until tomorrow. The piece I needed was the join on a.position and the :old.position. Thanks very much!

    if (updating and :old.description <> :new.description) then
    insert into position_change (position, company, employee, effect_date, department, job_code, description, locat_code, r_shift, typeofchange, change_dt, field_changed, old_value, end_date )
    values (:old.position, :old.company, (select a.employee from pas a, pos b where a.company = b.company and a.position = b.position and a.position = :old.position), :old.effect_date, :old.department, :old.job_code, :new.description, :old.locat_code, :old.r_shift, 'U', sysdate, 'description', :old.description, :old.end_date);
    end if;

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Please, when posting your future messages, format the code and enclose it within the [code] tags in order to maintain formatting and improve readability.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    INSERT INTO position_change
                (position,
                 company,
                 employee,
                 effect_date,
                 department,
                 job_code,
                 description,
                 locat_code,
                 r_shift,
                 typeofchange,
                 change_dt,
                 field_changed,
                 old_value,
                 end_date)
    VALUES      (:old.position,
                 :old.company,
                 (SELECT a.employee
                  FROM   pas a,
                         pos b
                  WHERE  a.company = b.company
                         AND a.position = b.position
                         AND a.position = :old.position),
                 :old.effect_date,
                 :old.department,
                 :old.job_code,
                 :new.description,
                 :old.locat_code,
                 :old.r_shift,
                 'U',
                 SYSDATE,
                 'description',
                 :old.description,
                 :old.end_date);
    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.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Not you, I know you know how to format code; I told it to dmcmillan.

Posting Permissions

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