This topic contains the following examples:
DML Trigger Example
DML Trigger Example with Restriction
Calling a Procedure in a Trigger Body Example
Database Event Trigger Example
DDL Trigger Example
INSTEAD OF Trigger Example

DML Trigger Example

This example creates a BEFORE statement trigger named EMP_PERMIT_CHANGES in the schema SCOTT. You would write this type of trigger to place restrictions on DML statements issued on this table (such as when these types of statements could be issued).
CREATE TRIGGER scott.emp_permit_changes
    BEFORE
    DELETE OR INSERT OR UPDATE
    ON scott.emp
       pl/sql_block
Oracle fires this trigger whenever a DELETE, INSERT, or UPDATE statement affects the EMP table in the schema SCOTT. The trigger EMP_PERMIT_CHANGES is a BEFORE statement trigger, so Oracle fires it once before executing the triggering statement.

DML Trigger Example with Restriction

This example creates a BEFORE row trigger named SALARY_CHECK in the schema SCOTT. The PL/SQL block might specify, for example, that the employee's salary must fall within the established salary range for the employee's job:
CREATE TRIGGER scott.salary_check
    BEFORE
    INSERT OR UPDATE OF sal, job ON scott.emp
    FOR EACH ROW
    WHEN (new.job <> 'PRESIDENT')
       pl/sql_block
Oracle fires this trigger whenever one of the following statements is issued:
-an INSERT statement that adds rows to the EMP table
-an UPDATE statement that changes values of the SAL or JOB columns of the EMP table
SALARY_CHECK is a BEFORE row trigger, so Oracle fires it before changing each row that is updated by the UPDATE statement or before adding each row that is inserted by the INSERT statement.
SALARY_CHECK has a trigger restriction that prevents it from checking the salary of the company president.

Calling a Procedure in a Trigger Body Example

You could create the SALARY_CHECK trigger described in the preceding example by calling a procedure instead of providing the trigger body in a PL/SQL block. Assume you have defined a procedure SCOTT.CHECK_SAL, which verifies that an employee's salary in in an appropriate range. Then you could create the trigger SALARY_CHECK as follows:
CREATE TRIGGER scott.salary_check
   BEFORE INSERT OR UPDATE OF sal, job ON scott.emp
   FOR EACH ROW
   WHEN (new.job <> 'PRESIDENT')
   CALL check_sal(:new.job, :new.sal, :new.ename);
The procedure CHECK_SAL could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD values in the CALL clause instead of :NEW values.

Database Event Trigger Example

This example creates a trigger to log all errors. The PL/SQL block does some special processing for a particular error (invalid logon, error number 1017. This trigger is an AFTER statement trigger, so it is fired after an unsuccessful statement execution (such as unsuccessful logon).
CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE
   BEGIN
      IF (IS_SERVERERROR (1017)) THEN
         <special processing of logon error>
      ELSE
         <log error number>>
      END IF;
   END;

DDL Trigger Example

This example creates an AFTER statement trigger on any DDL statement CREATE. Such a trigger can be used to audit the creation of new data dictionary objects in your schema.
CREATE TRIOGGER audit_db_object AFTER CREATE
   ON SCHEMA
      pl/sql_block

INSTEAD OF Trigger Example

In this example, customer data is stored in two tables. The object view ALL_CUSTOMERS is created as a UNION of the two tables, CUSTOMERS_SJ and CUSTOMERS_PA. An INSTEAD OF trigger is used to insert values.
CREATE TABLE customers_sj
  ( cust    NUMBER(6),
    address VARCHAR2(50),
    credit   NUMBER(9,2)  );

CREATE TABLE customers_pa
  ( cust    NUMBER(6),
    address VARCHAR2(50),
    credit   NUMBER(9,2) );

CREATE TYPE customer_t AS OBJECT
  ( cust    NUMBER(6),
    address   VARCHAR2(50),
    credit    NUMBER(9,2),
    location   VARCHAR2(20)  );

CREATE VIEW all_customers (cust)
    AS SELECT customer_t (cust, address, credit, 'SAN_JOSE')
    FROM   customers_sj
  UNION ALL
    SELECT customer_t (cust, address, credit, 'PALO_ALTO')
    FROM   customers_pa;

CREATE TRIGGER instrig INSTEAD OF INSERT ON all_customers
   FOR EACH ROW
     BEGIN
      IF (:new.cust.location = 'SAN_JOSE') THEN
        INSERT INTO customers_sj
        VALUES (:new.cust.cust, :new.cust.address,:new.cust.credit);
      ELSE
       INSERT INTO customers_pa
       VALUES (:new.cust.cust, :new.cust.address, :new.cust.credit);
      END IF;
    END;