Trying to create a trigger when there is an update of Status on Employee Table and capture some values for the record in Employee table and Employee_Header table for that record and send an email. The trigger throws an error.
CREATE OR REPLACE TRIGGER ODB.TRG_EMPLOYEE_STATUS_EMAIL AFTER UPDATE OF STATUS ON ODB.EMPLOYEE FOR EACH ROW DECLARE s_message varchar2(4000); s_subject varchar2(1000); s_return_message varchar2(4000); s_database varchar2(50); v_rm EMPLOYEE%ROWTYPE; v_sh EMPLOYEE_HEADER%ROWTYPE; BEGIN if :old."STATUS" = 'HOLD' AND :new."STATUS" = 'ACTIVE' AND :new."CATEGORY" = 'FULLTIME' then select * into v_rm from EMPLOYEE WHERE :new."STATUS" = 'ACTIVE' AND ROWNUM>1; select * into v_sh from EMPLOYEE_HEADER WHERE ROWNUM>1; s_subject := 'NAME ' || v_rm.NAME ||' message ' || ' CHECK LOG OF EMPLOYEE' || Chr(13) || ' STATUS: ' || v_rm.STATUS ; s_message := 'SAMPLE' || Chr(10)||Chr(13) || 'THE STATUS IN EMPLOYEE_HEADER IS: ' || Chr(10)|| ' STATUS: ' || v_sh.STATUS ; pkg_email.sendEmail('INPUT PARAMETERS TO SEND EMAIL'); end if; END;
Advertisement
Answer
You can’t select from a table which is just being changed; it is mutating. Though, as you can use the :new
pseudorecord, you can “skip” that error. Also, where rownum > 1
is useless as it is never true. I don’t know what you meant to say by using it.
I see you’ve created columns using double quotes. In Oracle, that’s usually a mistake. Not that it won’t work – it will, but you always have to reference columns using double quotes and match letter case.
Finally, trigger might look like this (read comments within code):
create or replace trigger odb.trg_employee_status_email after update of status on odb.employee for each row declare s_message varchar2(4000); s_subject varchar2(1000); s_return_message varchar2(4000); s_database varchar2(50); -- v_rm employee%rowtype; -- you don't need that v_sh employee_header%rowtype; begin if :old."status" = 'HOLD' and :new."status" = 'ACTIVE' and :new."category" = 'FULLTIME' then -- You can't select from a table which is just being changed - it is "mutating". -- Besides, AND ROWNUM > 1 will never return anything. You can only check -- ROWNUM <= some_value --select * into v_rm from employee where :new."status" = 'ACTIVE' and rownum>1; select * into v_sh from employee_header where rownum>1; -- instead of SELECT ... INTO V_RM, use :NEW pseudorecord s_subject := 'NAME ' || :new.name ||' message ' || ' CHECK LOG OF EMPLOYEE' || chr(13) || ' STATUS: ' || :new.status ; s_message := 'SAMPLE' || chr(10)||chr(13) || 'THE STATUS IN EMPLOYEE_HEADER IS: ' || chr(10)|| ' STATUS: ' || v_sh.status ; pkg_email.sendemail('INPUT PARAMETERS TO SEND EMAIL'); end if; exception when no_data_found then null; end;