Tutorial: Creating a Trigger that Logs Table Changes

This tutorial shows how to use the CREATE TRIGGER statement to create a trigger, EVAL_CHANGE_TRIGGER, which adds a row to the table EVALUATIONS_LOG whenever an INSERT, UPDATE, or DELETE statement changes the EVALUATIONS table.

The trigger adds the row after the triggering statement executes, and uses the conditional predicates INSERTING, UPDATING, and DELETING to determine which of the three possible DML statements fired the trigger.

EVAL_CHANGE_TRIGGER is a statement-level trigger and an AFTER trigger.

This trigger is part of the sample application that the tutorials and examples in this document show how to develop and deploy.

To create EVALUATIONS_LOG and EVAL_CHANGE_TRIGGER:

1. Create the EVALUATIONS_LOG table:

   CREATE TABLE EVALUATIONS_LOG ( log_date DATE
                                , action VARCHAR2(50));
   

2. Create EVAL_CHANGE_TRIGGER:

   CREATE OR REPLACE TRIGGER EVAL_CHANGE_TRIGGER
     AFTER INSERT OR UPDATE OR DELETE
     ON EVALUATIONS
   DECLARE
     log_action  EVALUATIONS_LOG.action%TYPE;
   BEGIN
     IF INSERTING THEN
       log_action := 'Insert';
     ELSIF UPDATING THEN
       log_action := 'Update';
     ELSIF DELETING THEN
       log_action := 'Delete';
     ELSE
       DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
     END IF;
   
     INSERT INTO EVALUATIONS_LOG (log_date, action)
       VALUES (SYSDATE, log_action);
   END;