Creating a Trigger With the BEFORE Option and WHEN Clause

In Example: Creating a Database Trigger With the BEFORE Option, you define a BEFORE trigger that is fired for each row that is updated. If there are five employees in department 20, and the salaries for all the employees in the department are updated, then the trigger fires five times when those rows are updated. Note the use of the WHEN clause to restrict the firing of the trigger.

Creating a Database Trigger With the BEFORE Option

-- create a temporary table
CREATE TABLE emp_sal_log (emp_id NUMBER, log_date DATE, 
             new_salary NUMBER, action VARCHAR2(50));

CREATE OR REPLACE TRIGGER log_salary_increase -- create a trigger
  BEFORE UPDATE of salary ON employees FOR EACH ROW
  WHEN (OLD.salary < 8000)
BEGIN
  INSERT INTO emp_sal_log (emp_id, log_date, new_salary, action)
    VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');
END;
/
-- update the salary with the following UPDATE statement
-- trigger fires for each row that is udpated
UPDATE employees SET salary = salary * 1.01 WHERE department_id = 60;

-- view the log table
SELECT * FROM emp_sal_log;