Creating a Trigger That Fires Once For Each Update

In Example: Creating a Trigger That Fires Only Once, the FOR EACH ROW clause is omitted so the trigger fires only once for each update of or insert into the employees table. Because there are two operations that fire the trigger, this example includes IF-THEN statements to log the specific operation that fired the trigger. The check for the INSERTING condition evaluates to TRUE only if the statement that fired the trigger is an INSERT statement. The check for the UPDATING condition evaluates to TRUE only if the statement that fired the trigger is an UPDATE statement.

Creating a Trigger That Fires Only Once

-- create a log table
CREATE TABLE emp_update_log (log_date DATE, action VARCHAR2(50));

-- create a trigger
CREATE OR REPLACE TRIGGER log_emp_update
  AFTER UPDATE OR INSERT ON employees
DECLARE
  v_action VARCHAR2(50);
BEGIN
  IF UPDATING THEN
    v_action := 'A row has been updated in the employees table';
  END IF;
  IF INSERTING THEN
    v_action := 'A row has been inserted in the employees table';
  END IF;
  INSERT INTO emp_update_log (log_date, action)
    VALUES (SYSDATE, v_action);
END;
/

-- fire the trigger with an update
UPDATE employees SET salary = salary * 1.01 WHERE department_id = 60;
INSERT INTO employees VALUES(14, 'Belden', 'Enrique', 'EBELDEN','555.111.2222',
   '31-AUG-05', 'AC_MGR', 9000, .1, 101, 110);

-- view the log table
SELECT * FROM emp_update_log;
-- clean up: remove the inserted record
DELETE FROM employees WHERE employee_id = 14;