CREATE TRIGGER

Purpose

Use the CREATE TRIGGER statement to create and enable a database trigger, which is:

• A stored PL/SQL block associated with a table, a schema, or the database or
• An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java

Oracle Database automatically executes a trigger when specified conditions occur.

When you create a trigger, the database enables it automatically. You can subsequently disable and enable a trigger with the DISABLE and ENABLE clause of the ALTER TRIGGER or ALTER TABLE statement.

Examples

Creating a DML Trigger: Examples This example shows the basic syntax for a BEFORE statement trigger. You would write such a trigger to place restrictions on DML statements issued on a table, for example, when such statements could be issued.

CREATE TRIGGER schema.trigger_name 
    BEFORE 
    DELETE OR INSERT OR UPDATE 
    ON schema.table_name 
       pl/sql_block

Oracle Database fires such a trigger whenever a DML statement affects the table. This trigger is a BEFORE statement trigger, so the database fires it once before executing the triggering statement.

The next example shows a partial BEFORE row trigger. The PL/SQL block might specify, for example, that an employee's salary must fall within the established salary range for the employee's job:

CREATE TRIGGER hr.salary_check
      BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees
      FOR EACH ROW
         WHEN (new.job_id <> 'AD_VP')
      pl/sql_block

Oracle Database fires this trigger whenever one of the following statements is issued:

• An INSERT statement that adds rows to the employees table
• An UPDATE statement that changes values of the salary or job
• • An UPDATE statement that changes values of the salary or job_id columns of the employees table
  salary_check is a BEFORE row trigger, so the database 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 condition that prevents it from checking the salary of the administrative vice president (AD_VP).
  Creating a 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 TRIGGER audit_db_object AFTER CREATE
     ON SCHEMA
        pl/sql_block
  

  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 check_sal in the hr schema, which verifies that an employee's salary is in an appropriate range. Then you could create the trigger salary_check as follows:

  CREATE TRIGGER salary_check
     BEFORE INSERT OR UPDATE OF salary, job_id ON employees
     FOR EACH ROW
     WHEN (new.job_id <> 'AD_VP')
     CALL check_sal(:new.job_id, :new.salary, :new.last_name)
  
  

  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.
• Creating a Database Event Trigger: Example This example shows the basic syntax for a trigger to log all errors. The hypothetical 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;
  

  Creating an INSTEAD OF Trigger: Example In this example, an oe.order_info view is created to display information about customers and their orders:

  CREATE VIEW order_info AS
     SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
            o.order_id, o.order_date, o.order_status
     FROM customers c, orders o 
     WHERE c.customer_id = o.customer_id;
  
  

  Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view. The PL/SQL trigger implementation is shown in italics.
• Creating a Database Event Trigger: Example This example shows the basic syntax for a trigger to log all errors. The hypothetical 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;
  

  Creating an INSTEAD OF Trigger: Example In this example, an oe.order_info view is created to display information about customers and their orders:

  CREATE VIEW order_info AS
     SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
            o.order_id, o.order_date, o.order_status
     FROM customers c, orders o 
     WHERE c.customer_id = o.customer_id;
  
  

  Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view. The PL/SQL trigger implementation is shown in italics.

• CREATE OR REPLACE TRIGGER order_info_insert
     INSTEAD OF INSERT ON order_info
     DECLARE
       duplicate_info EXCEPTION;
       PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
     BEGIN
       INSERT INTO customers
         (customer_id, cust_last_name, cust_first_name) 
       VALUES (
       :new.customer_id, 
       :new.cust_last_name,
       :new.cust_first_name);
     INSERT INTO orders (order_id, order_date, customer_id)
     VALUES (
       :new.order_id,
       :new.order_date,
       :new.customer_id);
     EXCEPTION
       WHEN duplicate_info THEN
         RAISE_APPLICATION_ERROR (
           num=> -20107,
           msg=> 'Duplicate customer or order ID');
     END order_info_insert;
  /

• 
  You can now insert into both base tables through the view (as long as all NOT NULL columns receive values):
  INSERT INTO order_info VALUES
     (999, 'Smith', 'John', 2500, '13-MAR-2001', 0);
  
  
  Creating a SCHEMA Trigger: Example The following example creates a BEFORE statement trigger on the sample schema hr. When a user connected as hr attempts to drop a database object, the database fires the trigger before dropping the object:
  CREATE OR REPLACE TRIGGER drop_trigger 
     BEFORE DROP ON hr.SCHEMA 
     BEGIN
        RAISE_APPLICATION_ERROR (
           num => -20000,
           msg => 'Cannot drop object');
     END;