e.g.
SQL>
create table employees_salary_log (employee_id number, cur_salary number(8,2), new_salary number(8,2), updated_date date);
SQL>
CREATE OR REPLACE TRIGGER HR.TBU_EMPLOYEES
BEFORE UPDATE
ON HR.EMPLOYEES
REFERENCING NEW AS NEW_ROW OLD AS OLD_ROW
FOR EACH ROW
DECLARE
BEGIN
IF :OLD_ROW.MANAGER_ID IS NOT NULL
THEN
INSERT INTO employees_salary_log
VALUES (:NEW_ROW.employee_id, :OLD_ROW.salary, :NEW_ROW.salary, SYSTIMESTAMP);
END IF;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (
-20001,
'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
NULL;
END;
/
update employees
set salary = 17100
where employee_id = 101
/
update employees
set salary = 35000
where employee_id = 100
/
commit
/
select * from employees_salary_log
-- only display employee who is not a manager
SQL>
create table employees_salary_log (employee_id number, cur_salary number(8,2), new_salary number(8,2), updated_date date);
SQL>
CREATE OR REPLACE TRIGGER HR.TBU_EMPLOYEES
BEFORE UPDATE
ON HR.EMPLOYEES
REFERENCING NEW AS NEW_ROW OLD AS OLD_ROW
FOR EACH ROW
DECLARE
BEGIN
IF :OLD_ROW.MANAGER_ID IS NOT NULL
THEN
INSERT INTO employees_salary_log
VALUES (:NEW_ROW.employee_id, :OLD_ROW.salary, :NEW_ROW.salary, SYSTIMESTAMP);
END IF;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (
-20001,
'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
NULL;
END;
/
update employees
set salary = 17100
where employee_id = 101
/
update employees
set salary = 35000
where employee_id = 100
/
commit
/
select * from employees_salary_log
-- only display employee who is not a manager