putting a select statement in a trigger

[dmcmillan]

I'm trying to use as select statment in a trigger to get info into another table when an update occurs on the table with the trigger, along with a couple constants. I can't get the pl/sql to work, any ideas on what I'm doing wrong? Thanks.

if (updating and ld.term_date <> :new.term_date) then
insert into employee_change (company, employee, emp_status, first_name, last_name, middle_init, nick_name, birthdate, sex, adj_hire_date, date_hired, wk_phone_nbr, wk_phone_ext, pager, email_address, term_date, typeofchange, change_dt, field_changed )
values (SELECT a.company,
a.employee,
a.EMP_STATUS,
a.FIRST_NAME,
a.LAST_NAME,
a.MIDDLE_INIT,
a.NICK_NAME,
test.PAEMPLOYEE.BIRTHDATE,
test.PAEMPLOYEE.SEX,
a.ADJ_HIRE_DATE,
a.DATE_HIRED,
test.PAEMPLOYEE.WK_PHONE_NBR,
test.PAEMPLOYEE.WK_PHONE_EXT,
test.hrcontnbr.PHONE AS Pager,
a.EMAIL_ADDRESS,
a.term_date
FROM test.EMPLOYEE a JOIN test.PAEMPLOYEE ON a.COMPANY = test.PAEMPLOYEE.COMPANY AND a.EMPLOYEE=test.PAEMPLOYEE.EMPLOYEE
left outer join test.HRCONTNBR on a.company = test.HRCONTNBR.COMPANY AND a.EMPLOYEE = test.HRCONTNBR.ID_NBR, 'U', sysdate, 'term_date');
end if;

[dmcmillan]

My real problem is getting info from two other tables. I could just use
values (ld.company, ld.employee, etc. for the info from the table that the trigger is on (test.employee), but how would I get the info from the other two tables?
Thanks very much for any ideas.

[PAVB]

Unfortunatelly you forgot to show the trigger definition.

Query appears not to reference any value on trigger affected table - other tables in query should be references as in any normal query.

[dmcmillan]

Is it just a syntax problem then? Thanks!

CREATE OR REPLACE TRIGGER DMCMILLAN.test_change_employee
AFTER INSERT OR UPDATE
ON TEST.EMPLOYEE
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW

/******************************************************************************
NAME: test_change_employee
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 10/13/2011 dmcmillan 1. Created this trigger.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: test_change_employee
Sysdate: 10/13/2011
Date and Time: 10/13/2011, 12:52:18 PM, and 10/13/2011 12:52:18 PM
Username: dmcmillan (set in TOAD Options, Proc Templates)
Table Name: EMPLOYEE (set in the "New PL/SQL Object" dialog)
Trigger Options: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
if inserting then
insert into employee_change (company, employee, emp_status, first_name, last_name, middle_init, nick_name, adj_hire_date, date_hired, email_address, typeofchange, change_dt)
values (:new.company, :new.employee, :new.emp_status, :new.first_name, :new.last_name, :new.middle_init, :new.nick_name, :new.adj_hire_date, :new.date_hired, :new.email_address, 'I', sysdate);
end if;

if (updating and ld.last_name <> :new.last_name) then
insert into employee_change (company, employee, emp_status, first_name, last_name, middle_init, nick_name, adj_hire_date, date_hired, email_address, term_date, birthdate, sex, wk_phone_nbr, wk_phone_ext, pager, typeofchange, change_dt, field_changed )
values (ld.company, ld.employee, ld.EMP_STATUS, ld.FIRST_NAME, :new.LAST_NAME, ld.MIDDLE_INIT, ld.NICK_NAME, ld.ADJ_HIRE_DATE, ld.DATE_HIRED, ld.EMAIL_ADDRESS, ld.term_date,
(select ('ld.birthdate') from test.employee, test.paemployee where test.employee.COMPANY = test.PAEMPLOYEE.COMPANY AND test.employee.EMPLOYEE=test.PAEMPLOYEE.EMPLOYEE),
(select SEX from test.EMPLOYEE a JOIN test.PAEMPLOYEE ON test.employee.COMPANY = test.PAEMPLOYEE.COMPANY AND test.employee.EMPLOYEE=test.PAEMPLOYEE.EMPLOYEE),
(select WK_PHONE_NBR FROM test.EMPLOYEE a JOIN test.PAEMPLOYEE ON a.COMPANY = test.PAEMPLOYEE.COMPANY AND a.EMPLOYEE=test.PAEMPLOYEE.EMPLOYEE),
(select WK_PHONE_EXT FROM test.EMPLOYEE a JOIN test.PAEMPLOYEE ON a.COMPANY = test.PAEMPLOYEE.COMPANY AND a.EMPLOYEE=test.PAEMPLOYEE.EMPLOYEE),
(select PHONE AS Pager FROM test.EMPLOYEE a left outer join test.HRCONTNBR on a.company = test.HRCONTNBR.COMPANY AND a.EMPLOYEE = test.HRCONTNBR.ID_NBR),
'U', sysdate, 'last_name');
end if;


END test_change_employee;



/

[PAVB]

Yes. Just a syntax/logic issue.

I'll suggest to test "insert" query alone, once it works it will work inside the trigger.

By the way, this trigger will add a lot of overhead - hope table doesn't get a lot of insert/updates.

[dmcmillan]

The insert works ok, but it isn't connecting to the other two tables. I think that's my problem. I'm missing something I need to do to select from the other tables.
And you are right, it won't run very often.
Thanks!