Like a stored procedure, a trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it.

While a trigger is enabled, the database automatically invokes it—that is, the trigger fires—whenever its triggering event occurs. While a trigger is disabled, it does not fire.

You create a trigger with the CREATE TRIGGER statement. You specify the triggering event in terms of triggering statements and the item on which they act. The trigger is said to be created on or defined on the item, which is either a table, a view, a schema, or the database. You also specify the timing point, which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects. By default, a trigger is created in the enabled state.

If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger.

A crossedition trigger is a DML trigger for use only in edition-based redefinition.

If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger.

A conditional trigger is a DML or system trigger that has a WHEN clause that specifies a SQL condition that the database evaluates for each row that the triggering statement affects.

When a trigger fires, tables that the trigger references might be undergoing changes made by SQL statements in other users' transactions. SQL statements running in triggers follow the same rules that standalone SQL statements do. Specifically:

• Queries in the trigger see the current read-consistent materialized view of referenced tables and any data changed in the same transaction.

• Updates in the trigger wait for existing data locks to be released before proceeding.

An INSTEAD OF trigger is either:

• A DML trigger created on either a noneditioning view or a nested table column of a noneditioning view

• A system trigger defined on a CREATE statement

The database fires the INSTEAD OF trigger instead of running the triggering statement.

Triggers let you customize your database management system.

For example, you can use triggers to:

• Automatically generate virtual column values

• Log events

• Gather statistics on table access

• Modify table data when DML statements are issued against views

• Enforce referential integrity when child and parent tables are on different nodes of a distributed database

• Publish information about database events, user events, and SQL statements to subscribing applications

• Prevent DML operations on a table after regular business hours

• Prevent invalid transactions

• Enforce complex business or referential integrity rules that you cannot define with constraints (see "How Triggers and Constraints Differ")

A DML trigger is created on either a table or view, and its triggering event is composed of the DML statements DELETE, INSERT, and UPDATE.

To create a trigger that fires in response to a MERGE statement, create triggers on the INSERT and UPDATE statements to which the MERGE operation decomposes.

A DML trigger is either simple or compound.

A simple DML trigger fires at exactly one of these timing points:

• Before the triggering statement runs

  (The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger.)

• After the triggering statement runs

  (The trigger is called an AFTER statement trigger or statement-level AFTER trigger.)

• Before each row that the triggering statement affects

  (The trigger is called a BEFORE each row trigger or row-level BEFORE trigger.)

• After each row that the triggering statement affects

  (The trigger is called an AFTER each row trigger or row-level AFTER trigger.)

A compound DML trigger created on a table or editioning view can fire at one, some, or all of the preceding timing points. Compound DML triggers help program an approach where you want the actions that you implement for the various timing points to share common data.

A simple or compound DML trigger that fires at row level can access the data in the row that it is processing. For details, see "Correlation Names and Pseudorecords".

An INSTEAD OF DML trigger is a DML trigger created on either a noneditioning view or a nested table column of a noneditioning view.

Except in an INSTEAD OF trigger, a triggering UPDATE statement can include a column list. With a column list, the trigger fires only when a specified column is updated. Without a column list, the trigger fires when any column of the associated table is updated.

A trigger that fires at row level can access the data in the row that it is processing by using correlation names. The default correlation names are OLD, NEW, and PARENT. To change the correlation names, use the REFERENCING clause of the CREATE TRIGGER statement (see "referencing_clause ::=").

If the trigger is created on a nested table, then OLD and NEW refer to the current row of the nested table, and PARENT refers to the current row of the parent table. If the trigger is created on a table or view, then OLD and NEW refer to the current row of the table or view, and PARENT is undefined.

OLD, NEW, and PARENT are also called pseudorecords, because they have record structure, but are allowed in fewer contexts than records are. The structure of a pseudorecord is table_name%ROWTYPE, where table_name is the name of the table on which the trigger is created (for OLD and NEW) or the name of the parent table (for PARENT).

In the trigger_body of a simple trigger or the tps_body of a compound trigger, a correlation name is a placeholder for a bind variable. Reference the field of a pseudorecord with this syntax:

:pseudorecord_name.field_name

In the WHEN clause of a conditional trigger, a correlation name is not a placeholder for a bind variable. Therefore, omit the colon in the preceding syntax.

Table 9-4 shows the values of OLD and NEW fields for the row that the triggering statement is processing.

The restrictions on pseudorecords are:

• A pseudorecord cannot appear in a record-level operation.

  For example, the trigger cannot include this statement:

  :NEW := NULL;
  

• A pseudorecord cannot be an actual subprogram parameter.

  (A pseudorecord field can be an actual subprogram parameter.)

• The trigger cannot change OLD field values.

  Trying to do so raises ORA-04085.

• If the triggering statement is DELETE, then the trigger cannot change NEW field values.

  Trying to do so raises ORA-04084.

• An AFTER trigger cannot change NEW field values, because the triggering statement runs before the trigger fires.

  Trying to do so raises ORA-04084.

A BEFORE trigger can change NEW field values before a triggering INSERT or UPDATE statement puts them in the table.

If a statement triggers both a BEFORE trigger and an AFTER trigger, and the BEFORE trigger changes a NEW field value, then the AFTER trigger "sees" that change.

DROP TABLE Emp_log;
CREATE TABLE Emp_log (
  Emp_id     NUMBER,
  Log_date   DATE,
  New_salary NUMBER,
  Action     VARCHAR2(20));
 

CREATE OR REPLACE TRIGGER log_salary_increase
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW
BEGIN
  INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
  VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');
END;
/

UPDATE employees
SET salary = salary + 1000.0
WHERE Department_id = 20;
 

2 rows updated.
 

SELECT * FROM Emp_log;
 

    EMP_ID LOG_DATE  NEW_SALARY ACTION
---------- --------- ---------- --------------------
       201 28-APR-10      13650 New Salary
       202 28-APR-10       6300 New Salary
 
2 rows selected.

CREATE OR REPLACE TRIGGER print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON employees
  FOR EACH ROW
  WHEN (NEW.job_id <> 'AD_PRES')  -- do not print information about President
DECLARE
  sal_diff  NUMBER;
BEGIN
  sal_diff  := :NEW.salary  - :OLD.salary;
  DBMS_OUTPUT.PUT(:NEW.last_name || ': ');
  DBMS_OUTPUT.PUT('Old salary = ' || :OLD.salary || ', ');
  DBMS_OUTPUT.PUT('New salary = ' || :NEW.salary || ', ');
  DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
END;
/

SELECT last_name, department_id, salary, job_id
FROM employees
WHERE department_id IN (10, 20, 90)
ORDER BY department_id, last_name;
 

LAST_NAME                 DEPARTMENT_ID     SALARY JOB_ID
------------------------- ------------- ---------- ----------
Whalen                               10       4200 AD_ASST
Fay                                  20       6000 MK_REP
Hartstein                            20      13000 MK_MAN
De Haan                              90      17000 AD_VP
King                                 90      24000 AD_PRES
Kochhar                              90      17000 AD_VP
 
6 rows selected.

UPDATE employees
SET salary = salary * 1.05
WHERE department_id IN (10, 20, 90);

Whalen: Old salary = 4200, New salary = 4410, Difference: 210
Hartstein: Old salary = 13000, New salary = 13650, Difference: 650
Fay: Old salary = 6000, New salary = 6300, Difference: 300
Kochhar: Old salary = 17000, New salary = 17850, Difference: 850
De Haan: Old salary = 17000, New salary = 17850, Difference: 850
 
6 rows updated.

SELECT salary FROM employees WHERE job_id = 'AD_PRES';

    SALARY
----------
     25200
 
1 row selected.

DROP TABLE tab1;
CREATE TABLE tab1 (c1 CLOB);
INSERT INTO tab1 VALUES ('<h1>HTML Document Fragment</h1><p>Some text.', 3);

CREATE OR REPLACE TRIGGER trg1
  BEFORE UPDATE ON tab1
  FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Old value of CLOB column: '||:OLD.c1);
  DBMS_OUTPUT.PUT_LINE('Proposed new value of CLOB column: '||:NEW.c1);

  :NEW.c1 := :NEW.c1 || TO_CLOB('<hr><p>Standard footer paragraph.');

  DBMS_OUTPUT.PUT_LINE('Final value of CLOB column: '||:NEW.c1);
END;
/ 

SET SERVEROUTPUT ON;
UPDATE tab1 SET c1 = '<h1>Different Document Fragment</h1><p>Different text.';

SELECT * FROM tab1;

CREATE TABLE new (
  field1  NUMBER,
  field2  VARCHAR2(20)
);

CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE UPDATE ON new
REFERENCING new AS Newest
FOR EACH ROW
BEGIN
  :Newest.Field2 := TO_CHAR (:newest.field1);
END;
/

A system trigger is created on either a schema or the database.

Its triggering event is composed of either DDL statements (listed in "ddl_event") or database operation statements (listed in "database_event").

A system trigger fires at exactly one of these timing points:

• Before the triggering statement runs

  (The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger.)

• After the triggering statement runs

  (The trigger is called a AFTER statement trigger or statement-level AFTER trigger.)

• Instead of the triggering CREATE statement

  (The trigger is called an INSTEAD OF CREATE trigger.)

Topics

• SCHEMA Triggers

• DATABASE Triggers

• INSTEAD OF CREATE Triggers

Triggers can invoke subprograms written in PL/SQL, C, and Java. The trigger in Example 9-4 invokes a PL/SQL subprogram. The trigger in Example 9-23 invokes a Java subprogram.

A subprogram invoked by a trigger cannot run transaction control statements, because the subprogram runs in the context of the trigger body.

If a trigger invokes an invoker rights (IR) subprogram, then the user who created the trigger, not the user who ran the triggering statement, is considered to be the current user. For information about IR subprograms, see "Invoker's Rights and Definer's Rights (AUTHID Property)".

If a trigger invokes a remote subprogram, and a time stamp or signature mismatch is found during execution of the trigger, then the remote subprogram does not run and the trigger is invalidated.

CREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2)
IS LANGUAGE Java
name 'thjvTriggers.beforeDelete (oracle.jdbc.NUMBER, oracle.jdbc.CHAR)';

CREATE OR REPLACE TRIGGER Pre_del_trigger BEFORE DELETE ON Tab 
FOR EACH ROW
CALL Before_delete (:OLD.Id, :OLD.Ename)
/

import java.sql.*
import java.io.*
import oracle.jdbc.*
import oracle.oracore.*
public class thjvTriggers
{
public static void
beforeDelete (NUMBER old_id, CHAR old_name)
Throws SQLException, CoreException
   {
   Connection conn = JDBCConnection.defaultConnection();
   Statement stmt = conn.CreateStatement();
   String sql = "insert into logtab values
   ("+ old_id.intValue() +", '"+ old_ename.toString() + ", BEFORE DELETE');
   stmt.executeUpdate (sql);
   stmt.close();
   return;
   }
}

The CREATE TRIGGER statement compiles the trigger and stores its code in the database. If a compilation error occurs, the trigger is still created, but its triggering statement fails, except in these cases:

• The trigger was created in the disabled state.

• The triggering event is AFTER STARTUP ON DATABASE.

• The triggering event is either AFTER LOGON ON DATABASE or AFTER LOGON ON SCHEMA, and someone logs on as SYSTEM.

To see trigger compilation errors, either use the SHOW ERRORS command in SQL*Plus or Enterprise Manager, or query the static data dictionary view *_ERRORS (described in Oracle Database Reference).

If a trigger does not compile successfully, then its exception handler cannot run. For an example, see "Remote Exception Handling".

If a trigger references another object, such as a subprogram or package, and that object is modified or dropped, then the trigger becomes invalid. The next time the triggering event occurs, the compiler tries to revalidate the trigger (for details, see Oracle Database Development Guide).

To recompile a trigger manually, use the ALTER TRIGGER statement, described in "ALTER TRIGGER Statement".

In most cases, if a trigger runs a statement that raises an exception, and the exception is not handled by an exception handler, then the database rolls back the effects of both the trigger and its triggering statement.

In the following cases, the database rolls back only the effects of the trigger, not the effects of the triggering statement (and logs the error in trace files and the alert log):

• The triggering event is either AFTER STARTUP ON DATABASE or BEFORE SHUTDOWN ON DATABASE.

• The triggering event is AFTER LOGON ON DATABASE and the user has the ADMINISTER DATABASE TRIGGER privilege.

• The triggering event is AFTER LOGON ON SCHEMA and the user either owns the schema or has the ALTER ANY TRIGGER privilege.

In the case of a compound DML trigger, the database rolls back only the effects of the triggering statement, not the effects of the trigger. However, variables declared in the trigger are re-initialized, and any values computed before the triggering statement was rolled back are lost.

CREATE OR REPLACE TRIGGER employees_tr
  AFTER INSERT ON employees
  FOR EACH ROW
BEGIN
  -- When remote database is unavailable, compilation fails here:
  INSERT INTO employees@remote (
    employee_id, first_name, last_name, email, hire_date, job_id
  ) 
  VALUES (
    99, 'Jane', 'Doe', 'jane.doe@example.com', SYSDATE, 'ST_MAN'
  );
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO emp_log (Emp_id, Log_date, New_salary, Action)
      VALUES (99, SYSDATE, NULL, 'Could not insert');
    RAISE;
END;
/

CREATE OR REPLACE PROCEDURE insert_row_proc AUTHID CURRENT_USER AS
  no_remote_db EXCEPTION;  -- declare exception
  PRAGMA EXCEPTION_INIT (no_remote_db, -20000);
                           -- assign error code to exception
BEGIN
  INSERT INTO employees@remote (
    employee_id, first_name, last_name, email, hire_date, job_id
  ) 
  VALUES (
    99, 'Jane', 'Doe', 'jane.doe@example.com', SYSDATE, 'ST_MAN'
  );
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO emp_log (Emp_id, Log_date, New_salary, Action)
      VALUES (99, SYSDATE, NULL, 'Could not insert row.');
 
  RAISE_APPLICATION_ERROR (-20000, 'Remote database is unavailable.');
END;
/
 
CREATE OR REPLACE TRIGGER employees_tr
  AFTER INSERT ON employees
  FOR EACH ROW
BEGIN
  insert_row_proc;
END;
/

• Use triggers to ensure that whenever a specific event occurs, any necessary actions are done (regardless of which user or application issues the triggering statement).

  For example, use a trigger to ensure that whenever anyone updates a table, its log file is updated.

• Do not create triggers that duplicate database features.

  For example, do not create a trigger to reject invalid data if you can do the same with constraints (see "How Triggers and Constraints Differ").

• Do not create triggers that depend on the order in which a SQL statement processes rows (which can vary).

  For example, do not assign a value to a global package variable in a row trigger if the current value of the variable depends on the row being processed by the row trigger. If a trigger updates global package variables, initialize those variables in a BEFORE statement trigger.

• Use BEFORE row triggers to modify the row before writing the row data to disk.

• Use AFTER row triggers to obtain the row ID and use it in operations.

  An AFTER row trigger fires when the triggering statement results in ORA-02292.

  Note:

  AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks are read first for the trigger and then for the triggering statement. With AFTER row triggers, affected data blocks are read only for the trigger.

• If the triggering statement of a BEFORE statement trigger is an UPDATE or DELETE statement that conflicts with an UPDATE statement that is running, then the database does a transparent ROLLBACK to SAVEPOINT and restarts the triggering statement. The database can do this many times before the triggering statement completes successfully. Each time the database restarts the triggering statement, the trigger fires. The ROLLBACK to SAVEPOINT does not undo changes to package variables that the trigger references. To detect this situation, include a counter variable in the package.

• Do not create recursive triggers.

  For example, do not create an AFTER UPDATE trigger that issues an UPDATE statement on the table on which the trigger is defined. The trigger fires recursively until it runs out of memory.

• If you create a trigger that includes a statement that accesses a remote database, then put the exception handler for that statement in a stored subprogram and invoke the subprogram from the trigger.

  For more information, see "Remote Exception Handling".

• Use DATABASE triggers judiciously. They fire every time any database user initiates a triggering event.

• If a trigger runs the following statement, the statement returns the owner of the trigger, not the user who is updating the table:

  SELECT Username FROM USER_USERS;
  

• Only committed triggers fire.

  A trigger is committed, implicitly, after the CREATE TRIGGER statement that creates it succeeds. Therefore, the following statement cannot fire the trigger that it creates:

  CREATE OR REPLACE TRIGGER my_trigger
    AFTER CREATE ON DATABASE
  BEGIN
    NULL;
  END;
  /
  

• To allow the modular installation of applications that have triggers on the same tables, create multiple triggers of the same type, rather than a single trigger that runs a sequence of operations.

  Each trigger sees the changes made by the previously fired triggers. Each trigger can see OLD and NEW values.

In addition to the restrictions that apply to all PL/SQL units (see Table C-1), triggers have these restrictions:

• Trigger Size Restriction

• Trigger LONG and LONG RAW Data Type Restrictions

• Mutating-Table Restriction

• Only an autonomous trigger can run TCL or DDL statements.

  For information about autonomous triggers, see "Autonomous Triggers".

• A trigger cannot invoke a subprogram that runs transaction control statements, because the subprogram runs in the context of the trigger body.

  For more information about subprograms invoked by triggers, see "Subprograms Invoked by Triggers".

• A trigger cannot access a SERIALLY_REUSABLE package.

  For information about SERIALLY_REUSABLE packages, see "SERIALLY_REUSABLE Packages".

If two or more triggers with different timing points are defined for the same statement on the same table, then they fire in this order:

1. All BEFORE STATEMENT triggers

2. All BEFORE EACH ROW triggers

3. All AFTER EACH ROW triggers

4. All AFTER STATEMENT triggers

If it is practical, replace the set of individual triggers with different timing points with a single compound trigger that explicitly codes the actions in the order you intend. For information about compound triggers, see "Compound DML Triggers".

If you are creating two or more triggers with the same timing point, and the order in which they fire is important, then you can control their firing order using the FOLLOWS and PRECEDES clauses (see "FOLLOWS | PRECEDES").

If multiple compound triggers are created on a table, then:

• All BEFORE STATEMENT sections run at the BEFORE STATEMENT timing point, BEFORE EACH ROW sections run at the BEFORE EACH ROW timing point, and so forth.

  If trigger execution order was specified using the FOLLOWS clause, then the FOLLOWS clause determines the order of execution of compound trigger sections. If FOLLOWS is specified for some but not all triggers, then the order of execution of triggers is guaranteed only for those that are related using the FOLLOWS clause.

• All AFTER STATEMENT sections run at the AFTER STATEMENT timing point, AFTER EACH ROW sections run at the AFTER EACH ROW timing point, and so forth.

  If trigger execution order was specified using the PRECEDES clause, then the PRECEDES clause determines the order of execution of compound trigger sections. If PRECEDES is specified for some but not all triggers, then the order of execution of triggers is guaranteed only for those that are related using the PRECEDES clause.

  Note:

  PRECEDES applies only to reverse crossedition triggers, which are described in Oracle Database Development Guide.

The firing of compound triggers can be interleaved with the firing of simple triggers.

When one trigger causes another trigger to fire, the triggers are said to be cascading. The database allows up to 32 triggers to cascade simultaneously. To limit the number of trigger cascades, use the initialization parameter OPEN_CURSORS (described in Oracle Database Reference), because a cursor opens every time a trigger fires.

By default, the CREATE TRIGGER statement creates a trigger in the enabled state. To create a trigger in the disabled state, specify DISABLE. Creating a trigger in the disabled state lets you ensure that it compiles without errors before you enable it.

ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE };

ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS;

The Oracle database utilities that transfer data to your database, possibly firing triggers, are:

• SQL*Loader (sqlldr)

  SQL*Loader loads data from external files into tables of an Oracle database.

  During a SQL*Loader conventional load, INSERT triggers fire.

  Before a SQL*Loader direct load, triggers are disabled.

  See Also:

  Oracle Database Utilities for more information about SQL*Loader

• Data Pump Import (impdp)

  Data Pump Import (impdp) reads an export dump file set created by Data Pump Export (expdp) and writes it to an Oracle database.

  If a table to be imported does not exist on the target database, or if you specify TABLE_EXISTS_ACTION=REPLACE, then impdp creates and loads the table before creating any triggers, so no triggers fire.

  If a table to be imported exists on the target database, and you specify either TABLE_EXISTS_ACTION=APPEND or TABLE_EXISTS_ACTION=TRUNCATE, then impdp loads rows into the existing table, and INSERT triggers created on the table fire.

  See Also:

  Oracle Database Utilities for more information about Data Pump Import

• Original Import (imp)

  Original Import (the original Import utility, imp) reads object definitions and table data from dump files created by original Export (the original Export utility, exp) and writes them to the target database.

  Note:

  To import files that original Export created, you must use original Import. In all other cases, Oracle recommends that you use Data Pump Import instead of original Import.

  If a table to be imported does not exist on the target database, then imp creates and loads the table before creating any triggers, so no triggers fire.

  If a table to be imported exists on the target database, then the Import IGNORE parameter determines whether triggers fire during import operations. The IGNORE parameter specifies whether object creation errors are ignored or not, resulting in the following behavior:

  • If IGNORE=n (default), then imp does not change the table and no triggers fire.

  • If IGNORE=y, then imp loads rows into the existing table, and INSERT triggers created on the table fire.

  See Also:

  • Oracle Database Utilities for more information about the original Import utility

  • Oracle Database Utilities for more information about the original Export utility

  • Oracle Database Utilities for more information about IGNORE

To use a trigger to publish an event, create a trigger that:

• Has the event as its triggering event

• Invokes the appropriate subprograms in the DBMS_AQ package, which provides an interface to Oracle Advanced Queuing (AQ)

  For information about the DBMS_AQ package, see Oracle Database PL/SQL Packages and Types Reference.

  For information about AQ, see Oracle Database Advanced Queuing User's Guide.

By enabling and disabling such triggers, you can turn event notification on and off. For information about enabling and disabling triggers, see "Trigger Enabling and Disabling".

The *_TRIGGERS static data dictionary views reveal information about triggers. For information about these views, see Oracle Database Reference.

CREATE OR REPLACE TRIGGER Emp_count
  AFTER DELETE ON employees
DECLARE
  n  INTEGER;
BEGIN
  SELECT COUNT(*) INTO n FROM employees;
  DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');
END;
/

COLUMN Trigger_type FORMAT A15
COLUMN Triggering_event FORMAT A16
COLUMN Table_name FORMAT A11
COLUMN Trigger_body FORMAT A50
SET LONG 9999

SELECT Trigger_type, Triggering_event, Table_name
FROM USER_TRIGGERS
WHERE Trigger_name = 'EMP_COUNT';

TRIGGER_TYPE    TRIGGERING_EVENT TABLE_NAME
--------------- ---------------- -----------
AFTER STATEMENT DELETE           EMPLOYEES

SELECT Trigger_body
FROM USER_TRIGGERS
WHERE Trigger_name = 'EMP_COUNT';

TRIGGER_BODY
--------------------------------------------------
DECLARE
  n  INTEGER;
BEGIN
  SELECT COUNT(*) INTO n FROM employees;
  DBMS_OUTPUT.PUT_LINE('There are now ' || n || '
employees.');
END;