Creating a Trigger in Oracle

A trigger is a procedure that is run automatically by the database when a specified SQL INSERT, UPDATE, or DELETE statement is run against a particular table. Triggers are useful for doing things like advanced auditing of changes made to column values in a table. When a Trigger Runs A trigger can fire before or after the SQL statement runs. A trigger may be run once for every row affected. Such a trigger is known as a row-level trigger. A trigger may be run for all the rows. Such trigger is known as a statement-level trigger. A row-level trigger has access to the old and new column values when the trigger fires as a result of an UPDATE statement on that column. The firing of a trigger may also be limited using a trigger condition. System Triggers There are a number of events where you can set system triggers such as ON LOGON, ON LOGOFF, ON STARTUP, ON DROP, ON TRUNCATE and so on. You can even track when any DDL command (CREATE, DROP, ALTER, and so on) was executed in the database. You may place system triggers at the database level or schema level. At the database level, triggers fire for each event for all users. At the schema level, triggers fire for each event for a specific user.

CREATE TABLE EMPLOYEES
(EMPNO NUMBER PRIMARY KEY, 
EMPNAME VARCHAR2(40),
DESIGNATION VARCHAR2(40),
SALARY NUMBER(10,2),
DEPTNO NUMBER);

Now we add some records in to the EMPLOYEES Table as follows:

INSERT INTO EMPLOYEES (EMPNO, EMPNAME, DESIGNATION, SALARY,DEPTNO)
VALUES (1001, 'NIMAL', 'MANAGER',  75000, 1);

INSERT INTO EMPLOYEES (EMPNO, EMPNAME, DESIGNATION, SALARY,DEPTNO)
VALUES (1002, 'SAMAN', 'ACCOUNTANT',  50000, 2);

INSERT INTO EMPLOYEES (EMPNO, EMPNAME, DESIGNATION, SALARY,DEPTNO)
VALUES (1003, 'AJITH', 'ENGINEER',  60000, 3);

Now we create AUDIT_TRAIL Table as follows:

CREATE TABLE AUDIT_TRAIL
(CHANGE_TYPE Varchar2(20),
USER_NAME Varchar2(40),
DATE_OF_EVENT date);

Now create trigger as follows:

SQL> CREATE OR REPLACE TRIGGER LOGCHANGES
  2    BEFORE INSERT OR DELETE OR UPDATE ON employee
  3    FOR EACH ROW
  4  DECLARE
  5    v_ChangeType VARCHAR2(20);
  6  BEGIN
  7    
  8    IF INSERTING THEN
  9      v_ChangeType := 'INSERT';
 10    ELSIF UPDATING THEN
 11      v_ChangeType := 'UPDATE';
 12    ELSE
 13      v_ChangeType := 'DELETE';
 14    END IF;
 15
 16    INSERT INTO AUFIT_TRAIL (CHANGE_TYPE, USER_NAME, DATE_OF_EVENT)
 17    VALUES (v_Change_Type, USER, SYSDATE);
 18    END LOGCHANGES;
 19  /

The trigger fires when we update, insert or delete records.

\

See also CREATING VIEWS IN ORACLE