Oracle : Trigger to Update

Question

I have two tables as follows

Emp(eno, ename, sal)
salHistory( empNo, ename, oldSal, newSal, salDif)

What I want to know is how to create a trigger (Name of the trigger is salary_change) that should be fired whenever the "Emp" table get UPDATE Statement to change the salary column (Sal) of an employee. Objective of this trigger is to write empNo, ename, oldSal, newSal, salDif to the table call SalHistory.

(In here salDif means the salary difference)

Thanks .....

Answer 1

First of all I'd suggest at least two changes to salhistory table:

1. Define saldif as a calculated column
2. Adding a column to store a datetime when the change occurred, otherwise you won't be able to tell one change from the other.

That being said the table schema for salhistory may look something like

CREATE TABLE salhistory
(
  empno NUMBER NOT NULL, 
  ename VARCHAR2(32) NOT NULL, 
  oldsal NUMBER(10,2) NOT NULL, 
  newsal NUMBER(10,2) NOT NULL, 
  saldif NUMBER(10,2) AS (newsal - oldsal),
  changed DATE NOT NULL,
  PRIMARY KEY (empno, changed)
);

Now the trigger

CREATE OR REPLACE TRIGGER tg_salary_history
AFTER UPDATE ON emp
FOR EACH ROW
WHEN (NEW.sal <> OLD.sal) -- log a salary change only if the salary has been actually changed
BEGIN
  INSERT INTO salhistory (empno, ename, oldsal, newsal, changed) VALUES
  (:OLD.eno, :OLD.ename, :OLD.sal, :NEW.sal, SYSDATE());
END;
/

Here is SQLFiddle demo