trigger on one table select field from another table

dmcmillan · #1 (**permalink**) 11-16-11, 14:11

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.

anacedent · #2 (**permalink**) 11-16-11, 15:33

>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.

dmcmillan · #3 (**permalink**) 11-16-11, 16:00

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.

Littlefoot · #4 (**permalink**) 11-16-11, 16:19

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>
);

anacedent · #5 (**permalink**) 11-16-11, 16:20

>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.

dmcmillan · #6 (**permalink**) 11-16-11, 16:44

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;

Littlefoot · #7 (**permalink**) 11-16-11, 16:57

Please, when posting your future messages, format the code and enclose it within the [code] tags in order to maintain formatting and improve readability.

anacedent · #8 (**permalink**) 11-16-11, 17:59

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);

Littlefoot · #9 (**permalink**) 11-17-11, 02:17

Not you, I know you know how to format code; I told it to dmcmillan.

Internet Services

Web Development

Digital Marketing

Consumer Tech