9 PL/SQL Triggers

Trigger Types

A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on.

An INSTEAD OF trigger is a DML trigger that is defined on a noneditioning view (not an editioning view or table). Oracle Database fires the INSTEAD OF trigger instead of running the triggering DML statement. For more information, see "INSTEAD OF Triggers".

A system trigger is defined on either a schema or the database. A trigger defined on a schema fires whenever any user connected as that schema owner initiates the triggering event. A trigger defined on a database fires whenever any database user initiates the triggering event.

A simple trigger can fire at exactly one of these timing points:

• Before the triggering event runs (statement-level BEFORE trigger)

• After the triggering event runs (statement-level AFTER trigger)

• Before each row that the event affects (row-level BEFORE trigger)

• After each row that the event affects (row-level AFTER trigger)

A compound trigger can fire at multiple timing points. Compound triggers make it easier to program an approach where you want the actions that you implement for the various timing points to share common data. For more information, see "Compound Triggers".

A crossedition trigger is used only in edition-based redefinition. For information about crossedition triggers, see Oracle Database Advanced Application Developer's Guide. This chapter describes only noncrossedition triggers.

OLD and NEW Pseudorecords

When a row-level trigger fires, the PL/SQL run-time system creates and populates the two pseudorecords OLD and NEW. They are called pseudorecords because they have some, but not all, of the properties of records. Their structure is table_name%ROWTYPE, where table_name is the name of the table on which the trigger is defined.

For the row that the trigger is processing:

• For an INSERT trigger, OLD contains no values, and NEW contains the new values.

• For an UPDATE trigger, OLD contains the old values, and NEW contains the new values.

• For a DELETE trigger, OLD contains the old values, and NEW contains no values.

To reference a pseudorecord, put a colon before its name—:OLD or :NEW—as in Example 9-1.

The ways in which pseudorecords are not like records are:

• You cannot change the values of OLD.

  (You can change the values of NEW.)

• You cannot pass OLD or NEW as a record parameter to a subprogram that the trigger calls.

  (You can pass fields of OLD and NEW as parameters to such subprograms.)

• You cannot perform record-level operations with OLD and NEW. For example, you cannot include this statement in a trigger:

  :NEW := NULL;
  

Trigger States

A trigger can be in either of two states:

Enabled. An enabled trigger runs its trigger body if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.

Disabled. A disabled trigger does not run its trigger body, even if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.

By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement.

Data Access for Triggers

When a trigger is fired, the tables referenced in the trigger action might be undergoing changes by SQL statements in other users' transactions. In all cases, the SQL statements running in triggers follow the common rules used for standalone SQL statements. In particular, if an uncommitted transaction has modified values that a trigger being fired either must read (query) or write (update), then the SQL statements in the body of the trigger being fired use these guidelines:

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

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

Trigger Uses

Triggers supplement the standard capabilities of your database to provide a highly customized database management system. For example, you can use triggers to:

• Automatically generate calculated column values

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

• Enforce complex business or referential integrity rules that you cannot define with constraints

• Provide transparent event logging

• Maintain synchronous table replicates

• Gather statistics on table access

• Modify table data when DML statements are issued against views

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

• Restrict DML operations against a table to those issued during regular business hours

• Prevent invalid transactions

• Enforce security authorizations

How Triggers and Constraints Differ

Both triggers and constraints can constrain data input, but they differ significantly.

A constraint is a statement about the database that is always true. A constraint applies to existing data in the table and to any statement that manipulates the table. For more information about constraints, see Oracle Database Advanced Application Developer's Guide.

A trigger constrains what a statement can do. A trigger does not apply to data that was loaded before the trigger was defined. Therefore, you might not know whether all data in a table conforms to the rules established by the trigger.

Constraints are easier to write and less error-prone than triggers that enforce the same rules. However, triggers can enforce some complex business rules that constraints cannot. Oracle strongly recommends that you use triggers to constrain data input only in these situations:

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

• To enforce complex business or referential integrity rules that you cannot define with integrity constraints. For information about integrity constraints, see Oracle Database SQL Language Reference.

Trigger Names

Triggers in the same schema cannot have the same names. Triggers can have the same names as other schema objects—for example, a table and a trigger can have the same name—however, to avoid confusion, this is not recommended.

Triggering Statements

A trigger fires based on a triggering statement, which specifies:

• The SQL statement, database event, or DDL event that fires the trigger body. The options include DELETE, INSERT, and UPDATE. One, two, or all three of these options can be included in the triggering statement specification.

• The table, view, DATABASE, or SCHEMA on which the trigger is defined.

  Note:

  Exactly one table or view can be specified in the triggering statement. If the INSTEAD OF option is used, then the triggering statement must specify a view; conversely, if a view is specified in the triggering statement, then only the INSTEAD OF option can be used.

In Example 9-1, the PRINT_SALARY_CHANGES trigger fires after any DELETE, INSERT, or UPDATE on the employees table.

Topics:

• SQL*Loader and Import

• How Column Lists Affect UPDATE Triggers

... BEFORE DELETE OR INSERT OR UPDATE OF last_name ON employees ...

BEFORE and AFTER Triggers

The BEFORE or AFTER option in the CREATE TRIGGER statement specifies exactly when to fire the trigger body in relation to the triggering statement that is being run. In a CREATE TRIGGER statement, the BEFORE or AFTER option is specified just before the triggering statement. For example, the PRINT_SALARY_CHANGES trigger in the previous example is a BEFORE trigger.

In general, you use BEFORE or AFTER triggers to achieve these results:

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

• Use AFTER row triggers to obtain, and perform operations, using the row ID.

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

  Note:

  AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with AFTER row triggers, the data blocks must be read only once for both the triggering statement and the trigger.

If an UPDATE or DELETE statement detects a conflict with a concurrent UPDATE, then the database performs a transparent ROLLBACK to SAVEPOINT and restarts the update. This can occur many times before the statement completes successfully. Each time the statement is restarted, the BEFORE statement trigger fires again. The rollback to savepoint does not undo changes to any package variables referenced in the trigger. Include a counter variable in your package to detect this situation.

INSTEAD OF Triggers

An INSTEAD OF trigger is a DML trigger defined on a noneditioning view, which Oracle Database fires instead of running the triggering DML statement.

An updatable view is one that lets you perform DML on the underlying table. Some views are inherently updatable, but others are not because they were created with one or more of the constructs listed in "Views that Require INSTEAD OF Triggers".

Any noneditioning view that contains one of those constructs can be made updatable by using an INSTEAD OF trigger. INSTEAD OF triggers provide a transparent way of modifying noneditioning views that cannot be modified directly through UPDATE, INSERT, and DELETE statements. These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle Database fires the trigger instead of running the triggering statement. The trigger must determine what operation was intended and perform UPDATE, INSERT, or DELETE operations directly on the underlying tables.

With an INSTEAD OF trigger, you can write normal UPDATE, INSERT, and DELETE statements against the noneditioning view, and the INSTEAD OF trigger performs the correct actions.

INSTEAD OF triggers can only be activated for each row.

Topics:

• Views that Require INSTEAD OF Triggers

• Triggers on Nested Table View Columns

• INSTEAD OF Trigger Example

-- Create type of nested table element:
 
CREATE OR REPLACE TYPE nte
AUTHID DEFINER IS
OBJECT (
  emp_id     NUMBER(6),
  lastname   VARCHAR2(25),
  job        VARCHAR2(10),
  sal        NUMBER(8,2)
);
/
 
-- Created type of nested table:
 
CREATE OR REPLACE TYPE emp_list_ IS
  TABLE OF nte;
/
 
-- Create view:

CREATE OR REPLACE VIEW dept_view AS
  SELECT d.department_id, 
         d.department_name,
         CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, e.salary
                         FROM employees e
                         WHERE e.department_id = d.department_id
                        )
                        AS emp_list_
              ) emplist
  FROM departments d;

CREATE OR REPLACE TRIGGER dept_emplist_tr
  INSTEAD OF INSERT ON NESTED TABLE emplist OF dept_view
  REFERENCING NEW AS Employee
  PARENT AS Department
  FOR EACH ROW
BEGIN
  -- Insert on nested table translates to insert on base table:
  INSERT INTO employees (
    employee_id,
    last_name,
    email,
    hire_date,
    job_id,
    salary,
    department_id
  )
  VALUES (
    :Employee.emp_id,                      -- employee_id
    :Employee.lastname,                    -- last_name
    :Employee.lastname || '@company.com',  -- email
    SYSDATE,                               -- hire_date
    :Employee.job,                         -- job_id
    :Employee.sal,                         -- salary                    
    :Department.department_id              -- department_id
  );
END;
/

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE department_id=10;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        200 Whalen                    AD_ASST          4288
 
1 row selected.

INSERT INTO TABLE (
  SELECT d.emplist 
  FROM dept_view d
  WHERE department_id = 10
)
VALUES (1001, 'Glenn', 'AC_MGR', 10000);

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        200 Whalen                    AD_ASST          4288
       1001 Glenn                     AC_MGR          10000
 
2 rows selected.

DROP TABLE Project_tab;
CREATE TABLE Project_tab (
  Prj_level NUMBER,
  Projno    NUMBER,
  Resp_dept NUMBER);

DROP TABLE emp;
CREATE TABLE emp (
  Empno     NUMBER NOT NULL,
  Ename     VARCHAR2(10),
  Job       VARCHAR2(9),
  Mgr       NUMBER(4),
  Hiredate  DATE,
  Sal       NUMBER(7,2),
  Comm      NUMBER(7,2),
  Deptno    NUMBER(2) NOT NULL);

DROP TABLE dept;
CREATE TABLE dept (
  Deptno    NUMBER(2) NOT NULL,
  Dname     VARCHAR2(14),
  Loc       VARCHAR2(13),
  Mgr_no    NUMBER,
  Dept_type NUMBER);

CREATE OR REPLACE VIEW manager_info AS
  SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level, p.projno
  FROM emp e, dept d, Project_tab p
  WHERE e.empno =  d.mgr_no
  AND d.deptno = p.resp_dept;

CREATE OR REPLACE TRIGGER manager_info_insert
  INSTEAD OF INSERT ON manager_info
    REFERENCING NEW AS n  -- new manager information
      FOR EACH ROW
DECLARE
  rowcnt number;
BEGIN
  SELECT COUNT(*) INTO rowcnt FROM emp WHERE empno = :n.empno;
  IF rowcnt = 0  THEN
    INSERT INTO emp (empno,ename) VALUES (:n.empno, :n.ename);
  ELSE
    UPDATE emp SET emp.ename = :n.ename WHERE emp.empno = :n.empno;
  END IF;
  SELECT COUNT(*) INTO rowcnt FROM dept WHERE deptno = :n.deptno;
  IF rowcnt = 0 THEN
    INSERT INTO dept (deptno, dept_type) 
      VALUES(:n.deptno, :n.dept_type);
  ELSE
    UPDATE dept SET dept.dept_type = :n.dept_type
      WHERE dept.deptno = :n.deptno;
  END IF;
  SELECT COUNT(*) INTO rowcnt FROM Project_tab
    WHERE Project_tab.projno = :n.projno;
  IF rowcnt = 0 THEN
    INSERT INTO Project_tab (projno, prj_level) 
      VALUES(:n.projno, :n.prj_level);
  ELSE
    UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level
      WHERE Project_tab.projno = :n.projno;
  END IF;
END;
/

FOR EACH ROW Triggers

If you specify FOR EACH ROW, the trigger is a row-level trigger; it fires once for each row of the table that the triggering statement affects. Without FOR EACH ROW, the trigger is a statement-level trigger; it fires only once for each triggering statement, not separately for each affected row. Statement-level triggers are useful for performing validation checks for the entire statement.

Example 9-3 creates a FOR EACH ROW trigger that inserts a row in a log table each time an UPDATE statement affects a row of the EMPLOYEES table.

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 ON employees
  FOR EACH ROW
  WHEN (NEW.salary > 1000)
BEGIN
  INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
  VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'NEW SAL');
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 10-MAR-09      14260 NEW SAL
       202 10-MAR-09       7120 NEW SAL
 
2 rows selected.

Conditional Triggers

Optionally, a trigger restriction can be included in the definition of a row trigger by specifying a Boolean SQL expression in a WHEN clause.

If included, then the expression in the WHEN clause is evaluated for each row that the trigger affects.

If the expression evaluates to TRUE for a row, then the trigger body runs on behalf of that row. However, if the expression evaluates to FALSE or NOT TRUE for a row (unknown, as with nulls), then the trigger body does not run for that row. The evaluation of the WHEN clause does not affect the execution of the triggering SQL statement (in other words, the triggering statement is not rolled back if the expression in a WHEN clause evaluates to FALSE).

For example, in the PRINT_SALARY_CHANGES trigger, the trigger body is not run if the new value of employee_id is zero, NULL, or negative. In more realistic examples, you might test if one column value is less than another.

The expression in a WHEN clause of a row trigger can include correlation names, which are explained in "Trigger Body". The expression in a WHEN clause must be a SQL expression, and it cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause.

Compound Triggers

A compound trigger can fire at multiple timing points.

Topics:

• Compound Trigger Uses

• Compound Trigger Sections

• Triggering Statements of Compound Triggers

• Compound Trigger Restrictions

• Compound Trigger Example

• Using Compound Triggers to Avoid Mutating-Table Error

CREATE OR REPLACE TRIGGER compound_trigger
  FOR UPDATE OF salary ON employees
  COMPOUND TRIGGER
  
  -- Declarative part (optional)
  -- Variables declared here have firing-statement duration.
  threshold CONSTANT SIMPLE_INTEGER := 200;
  
  BEFORE STATEMENT IS
  BEGIN
    NULL;
  END BEFORE STATEMENT;
 
  BEFORE EACH ROW IS
  BEGIN
    NULL;
  END BEFORE EACH ROW;
 
  AFTER EACH ROW IS
  BEGIN
    NULL;
  END AFTER EACH ROW;
 
  AFTER STATEMENT IS
  BEGIN
    NULL;
  END AFTER STATEMENT;
END compound_trigger;
/

INSERT INTO Target
  SELECT c1, c2, c3
  FROM Source
  WHERE Source.c1 > 0

CREATE TABLE employee_salaries (
  employee_id NUMBER NOT NULL,
  change_date DATE   NOT NULL,
  salary NUMBER(8,2) NOT NULL,
  CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
  CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
    REFERENCES employees (employee_id)
      ON DELETE CASCADE)
/
CREATE OR REPLACE TRIGGER maintain_employee_salaries
  FOR UPDATE OF salary ON employees
    COMPOUND TRIGGER

-- Declarative Part:
-- Choose small threshhold value to show how example works:
  threshhold CONSTANT SIMPLE_INTEGER := 7;

  TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
  salaries  salaries_t;
  idx       SIMPLE_INTEGER := 0;

  PROCEDURE flush_array IS
    n CONSTANT SIMPLE_INTEGER := salaries.count();
  BEGIN
    FORALL j IN 1..n
      INSERT INTO employee_salaries VALUES salaries(j);
    salaries.delete();
    idx := 0;
    DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows');
  END flush_array;

  -- AFTER EACH ROW Section:

  AFTER EACH ROW IS
  BEGIN
    idx := idx + 1;
    salaries(idx).employee_id := :NEW.employee_id;
    salaries(idx).change_date := SYSDATE();
    salaries(idx).salary := :NEW.salary;
    IF idx >= threshhold THEN
      flush_array();
    END IF;
  END AFTER EACH ROW;

  -- AFTER STATEMENT Section:

  AFTER STATEMENT IS
  BEGIN
    flush_array();
  END AFTER STATEMENT;
END maintain_employee_salaries;
/
/* Increase salary of every employee in department 50 by 10%: */

UPDATE employees
  SET salary = salary * 1.1
  WHERE department_id = 50
/

/* Wait two seconds: */

BEGIN
  DBMS_LOCK.SLEEP(2);
END;
/

/* Increase salary of every employee in department 50 by 5%: */

UPDATE employees
  SET salary = salary * 1.05
  WHERE department_id = 50
/

CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
  FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
  Ten_Percent                 CONSTANT NUMBER := 0.1;
  TYPE Salaries_t             IS TABLE OF Employees.Salary%TYPE;
  Avg_Salaries                Salaries_t;
  TYPE Department_IDs_t       IS TABLE OF Employees.Department_ID%TYPE;
  Department_IDs              Department_IDs_t;

  TYPE Department_Salaries_t  IS TABLE OF Employees.Salary%TYPE
                                INDEX BY VARCHAR2(80);
  Department_Avg_Salaries     Department_Salaries_t;

  BEFORE STATEMENT IS
  BEGIN
    SELECT               AVG(e.Salary), NVL(e.Department_ID, -1)
      BULK COLLECT INTO  Avg_Salaries, Department_IDs
      FROM               Employees e
      GROUP BY           e.Department_ID;
    FOR j IN 1..Department_IDs.COUNT() LOOP
      Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
    END LOOP;
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    IF :NEW.Salary - :Old.Salary >
      Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
    THEN
      Raise_Application_Error(-20000, 'Raise too big');
    END IF;
  END AFTER EACH ROW;
END Check_Employee_Salary_Raise;

Trigger Body

The trigger body is either a CALL subprogram (a PL/SQL subprogram, or a Java subprogram encapsulated in a PL/SQL wrapper) or a PL/SQL block, and as such, it can include SQL and PL/SQL statements. These statements run if the triggering statement is entered and if the trigger restriction (if any) evaluates to TRUE.

If the trigger body for a row trigger is a PL/SQL block (not a CALL subprogram), it can include these constructs:

• REFERENCING clause, which can specify the correlation names OLD, NEW, and PARENT

• Conditional predicates (see "Conditional Predicates for Detecting the DML Operation that Fired a Trigger")

SELECT Username FROM USER_USERS;

The LOGON trigger in Example 9-7 runs the procedure check_user after a user logs onto the database. The body of the trigger includes an exception-handling part, which includes a WHEN OTHERS exception that invokes RAISE_APPLICATION_ERROR.

CREATE OR REPLACE TRIGGER check_user
  AFTER LOGON ON DATABASE
  BEGIN
    check_user;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR
        (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
 END;
/

Although triggers are declared using PL/SQL, they can call subprograms in other languages. The trigger in Example 9-8 invokes a Java subprogram.

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

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

The corresponding Java file is thjvTriggers.java:

import java.sql.*
import java.io.*
import oracle.sql.*
import oracle.oracore.*
public class thjvTriggers
{
public state 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;
   }
}

Topics:

• Column Values Accessible to Row Triggers

• OBJECT_VALUE Pseudocolumn

• Remote Exception Handling

• Trigger Body Restrictions

IF :NEW.Sal > 10000 ...
IF :NEW.Sal < :OLD.Sal ...

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

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;
/

IF INSERTING OR UPDATING THEN ... ELSE ... END IF;

CASE
    WHEN INSERTING THEN ... ;
    WHEN UPDATING('salary') THEN ... ;
    WHEN UPDATING('department_id') THEN ... ;
    WHEN DELETING THEN ... ;
  END CASE;

CREATE OR REPLACE TYPE t AS OBJECT (n NUMBER, m NUMBER)
/
CREATE TABLE tbl OF t
/
BEGIN
  FOR j IN 1..5 LOOP
    INSERT INTO tbl VALUES (t(j, 0));
  END LOOP;
END;
/
SELECT * FROM tbl;

N          M
---------- ----------
         1          0
         2          0
         3          0
         4          0
         5          0

5 rows selected.

CREATE TABLE tbl_history ( d DATE, old_obj t, new_obj t)
/
CREATE OR REPLACE TRIGGER Tbl_Trg
  AFTER UPDATE ON tbl
  FOR EACH ROW
BEGIN
  INSERT INTO tbl_history (d, old_obj, new_obj)
  VALUES (SYSDATE, :OLD.OBJECT_VALUE, :NEW.OBJECT_VALUE);
END Tbl_Trg;
/
 

UPDATE tbl SET tbl.n = tbl.n+1
/
 

5 rows updated.

BEGIN
  FOR j IN (SELECT d, old_obj, new_obj FROM tbl_history) LOOP
    DBMS_OUTPUT.PUT_LINE (
      j.d ||
      ' -- old: ' || j.old_obj.n || ' ' || j.old_obj.m ||
      ' -- new: ' || j.new_obj.n || ' ' || j.new_obj.m
    );
  END LOOP;
END;
/

10-MAR-09 -- old: 1 0 -- new: 2 0
10-MAR-09 -- old: 2 0 -- new: 3 0
10-MAR-09 -- old: 3 0 -- new: 4 0
10-MAR-09 -- old: 4 0 -- new: 5 0
10-MAR-09 -- old: 5 0 -- new: 6 0

CREATE OR REPLACE TRIGGER employees_tr
  AFTER INSERT ON employees
  FOR EACH ROW
BEGIN
  -- When dblink is inaccessible, compilation fails here:
  INSERT INTO employees@remote (
    employee_id, first_name, last_name, email, hire_date, job_id
  ) 
  VALUES (
    99, 'Jane', 'Doe', 'jane.doe@acme.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');
END;
/

CREATE OR REPLACE PROCEDURE insert_row_proc AS
BEGIN
  INSERT INTO employees@remote (
    employee_id, first_name, last_name, email, hire_date, job_id
  ) 
  VALUES (
    99, 'Jane', 'Doe', 'jane.doe@acme.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');
END;
/
 
CREATE OR REPLACE TRIGGER employees_tr
  AFTER INSERT ON employees
  FOR EACH ROW
BEGIN
  insert_row_proc;
END;
/

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

DELETE FROM employees WHERE employee_id = 197
            *
ERROR at line 1:
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "HR.EMP_COUNT", line 4
ORA-04088: error during execution of trigger 'HR.EMP_COUNT'

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;
/
 
DELETE FROM employees WHERE employee_id = 197;

There are now 106 employees.
 
1 row deleted.

DROP TABLE p;
CREATE TABLE p (p1 NUMBER CONSTRAINT pk_p_p1 PRIMARY KEY);

DROP TABLE f;
CREATE TABLE f (f1 NUMBER CONSTRAINT fk_f_f1 REFERENCES p);

CREATE TRIGGER pt
  AFTER UPDATE ON p
  FOR EACH ROW
BEGIN
  UPDATE f SET f1 = :NEW.p1 WHERE f1 = :OLD.p1;
END;
/

UPDATE p SET p1 = p1+1;

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

Trigger Dependencies

Compiled triggers have dependencies. They become invalid if a depended-on object, such as a stored subprogram invoked from the trigger body, is modified. Triggers that are invalidated for dependency reasons are recompiled when next invoked.

You can examine the ALL_DEPENDENCIES view to see the dependencies for a trigger. For example, this statement shows the dependencies for the triggers in the HR schema:

SELECT NAME, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
FROM ALL_DEPENDENCIES
WHERE OWNER = 'HR' and TYPE = 'TRIGGER';

Triggers might depend on other functions or packages. If the function or package specified in the trigger is dropped, then the trigger is marked invalid. An attempt is made to validate the trigger on occurrence of the event. If the trigger cannot be validated successfully, then it is marked VALID WITH ERRORS, and the event fails. For more information about dependencies between schema objects, see Oracle Database Advanced Application Developer's Guide.

Trigger Recompilation

Use the ALTER TRIGGER statement to recompile a trigger manually. For example, this statement recompiles the PRINT_SALARY_CHANGES trigger:

ALTER TRIGGER Print_salary_changes COMPILE;

To recompile a trigger, you must own the trigger or have the ALTER ANY TRIGGER system privilege.

Foreign Key Trigger for Child Table

The trigger in Example 9-18 guarantees that before an INSERT or UPDATE statement affects a foreign key value, the corresponding value exists in the parent key. The mutating table exception included in Example 9-18 allows the trigger emp_dept_check to be used with the UPDATE_SET_DEFAULT and UPDATE_CASCADE triggers. This exception can be removed if the trigger emp_dept_check is used alone.

CREATE OR REPLACE TRIGGER emp_dept_check
  BEFORE INSERT OR UPDATE OF Deptno ON emp
  FOR EACH ROW WHEN (new.Deptno IS NOT NULL)

  -- Before row is inserted or DEPTNO is updated in emp table,
  -- fire this trigger to verify that new foreign key value (DEPTNO)
  -- is present in dept table.
DECLARE
  Dummy               INTEGER;  -- Use for cursor fetch
  Invalid_department  EXCEPTION;
  Valid_department    EXCEPTION;
  Mutating_table      EXCEPTION;
  PRAGMA EXCEPTION_INIT (Mutating_table, -4091);

  -- Cursor used to verify parent key value exists.
  -- If present, lock parent key's row so it cannot be deleted
  -- by another transaction until this transaction is
  -- committed or rolled back.

  CURSOR Dummy_cursor (Dn NUMBER) IS
    SELECT Deptno FROM dept
    WHERE Deptno = Dn
    FOR UPDATE OF Deptno;
BEGIN
  OPEN Dummy_cursor (:NEW.Deptno);
  FETCH Dummy_cursor INTO Dummy;

  -- Verify parent key.
  -- If not found, raise user-specified error number & message.
  -- If found, close cursor before allowing triggering statement to complete:

  IF Dummy_cursor%NOTFOUND THEN
    RAISE Invalid_department;
  ELSE
    RAISE valid_department;
  END IF;
  CLOSE Dummy_cursor;
EXCEPTION
  WHEN Invalid_department THEN
    CLOSE Dummy_cursor;
    Raise_application_error(-20000, 'Invalid Department'
      || ' Number' || TO_CHAR(:NEW.deptno));
  WHEN Valid_department THEN
    CLOSE Dummy_cursor;
  WHEN Mutating_table THEN
    NULL;
END;
/

UPDATE and DELETE RESTRICT Triggers for Parent Table

The trigger in Example 9-19 enforces the UPDATE and DELETE RESTRICT referential action on the primary key of the dept table.

CREATE OR REPLACE TRIGGER dept_restrict
  BEFORE DELETE OR UPDATE OF Deptno ON dept
  FOR EACH ROW

  -- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
  -- check for dependent foreign key values in emp;
  -- if any are found, roll back.

DECLARE
  Dummy                  INTEGER;  -- Use for cursor fetch
  Employees_present      EXCEPTION;
  employees_not_present  EXCEPTION;

  -- Cursor used to check for dependent foreign key values.
  CURSOR Dummy_cursor (Dn NUMBER) IS
    SELECT Deptno FROM emp WHERE Deptno = Dn;

BEGIN
  OPEN Dummy_cursor (:OLD.Deptno);
  FETCH Dummy_cursor INTO Dummy;

  -- If dependent foreign key is found, raise user-specified
  -- error number and message. If not found, close cursor
  -- before allowing triggering statement to complete.

  IF Dummy_cursor%FOUND THEN
    RAISE Employees_present;     -- Dependent rows exist
  ELSE
    RAISE Employees_not_present; -- No dependent rows exist
  END IF;
  CLOSE Dummy_cursor;

EXCEPTION
  WHEN Employees_present THEN
    CLOSE Dummy_cursor;
    Raise_application_error(-20001, 'Employees Present in'
      || ' Department ' || TO_CHAR(:OLD.DEPTNO));
  WHEN Employees_not_present THEN
    CLOSE Dummy_cursor;
END;

UPDATE and DELETE SET NULL Triggers for Parent Table

The trigger in Example 9-20 enforces the UPDATE and DELETE SET NULL referential action on the primary key of the dept table.

CREATE OR REPLACE TRIGGER dept_set_null
  AFTER DELETE OR UPDATE OF Deptno ON dept
  FOR EACH ROW

  -- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
  -- set all corresponding dependent foreign key values in emp to NULL:

BEGIN
  IF UPDATING AND :OLD.Deptno != :NEW.Deptno OR DELETING THEN
    UPDATE emp SET emp.Deptno = NULL
    WHERE emp.Deptno = :OLD.Deptno;
  END IF;
END;
/

DELETE Cascade Trigger for Parent Table

The trigger in Example 9-21 enforces the DELETE CASCADE referential action on the primary key of the dept table.

CREATE OR REPLACE TRIGGER dept_del_cascade
  AFTER DELETE ON dept
  FOR EACH ROW

  -- Before row is deleted from dept,
  -- delete all rows from emp table whose DEPTNO is same as
  -- DEPTNO being deleted from dept table:

BEGIN
  DELETE FROM emp
  WHERE emp.Deptno = :OLD.Deptno;
END;
/

UPDATE Cascade Trigger for Parent Table

The triggers in Example 9-22 ensure that if a department number is updated in the dept table, then this change is propagated to dependent foreign keys in the emp table.

-- Generate sequence number to be used as flag
-- for determining if update occurred on column:

CREATE SEQUENCE Update_sequence
  INCREMENT BY 1 MAXVALUE 5000 CYCLE;

CREATE OR REPLACE PACKAGE Integritypackage AS
  Updateseq NUMBER;
END Integritypackage;
/
CREATE OR REPLACE PACKAGE BODY Integritypackage AS
END Integritypackage;
/
-- Create flag col:

ALTER TABLE emp ADD Update_id NUMBER;

CREATE OR REPLACE TRIGGER dept_cascade1
  BEFORE UPDATE OF Deptno ON dept
DECLARE
  -- Before updating dept table (this is a statement trigger),
  -- generate sequence number
  -- & assign it to public variable UPDATESEQ of
  -- user-defined package named INTEGRITYPACKAGE:
BEGIN
  Integritypackage.Updateseq := Update_sequence.NEXTVAL;
END;
/
CREATE OR REPLACE TRIGGER dept_cascade2
  AFTER DELETE OR UPDATE OF Deptno ON dept
  FOR EACH ROW

  -- For each department number in dept that is updated,
  -- cascade update to dependent foreign keys in emp table.
  -- Cascade update only if child row was not updated by this trigger:
BEGIN
  IF UPDATING THEN
    UPDATE emp
    SET Deptno = :NEW.Deptno,
        Update_id = Integritypackage.Updateseq   --from 1st
    WHERE emp.Deptno = :OLD.Deptno
    AND Update_id IS NULL;

    /* Only NULL if not updated by 3rd trigger
       fired by same triggering statement */
  END IF;
  IF DELETING THEN
    -- Before row is deleted from dept,
    -- delete all rows from emp table whose DEPTNO is same as
    -- DEPTNO being deleted from dept table:
    DELETE FROM emp
    WHERE emp.Deptno = :OLD.Deptno;
  END IF;
END;
/
CREATE OR REPLACE TRIGGER dept_cascade3
  AFTER UPDATE OF Deptno ON dept
BEGIN UPDATE emp
  SET Update_id = NULL
  WHERE Update_id = Integritypackage.Updateseq;
END;
/

Triggers for Complex Check Constraints

Triggers can enforce integrity rules other than referential integrity. The trigger in Example 9-23 performs a complex check before allowing the triggering statement to run.

CREATE TABLE Salgrade (
  Grade               NUMBER,
  Losal               NUMBER,
  Hisal               NUMBER,
  Job_classification  NUMBER);

CREATE OR REPLACE TRIGGER salary_check
  BEFORE INSERT OR UPDATE OF Sal, Job ON Emp
  FOR EACH ROW
DECLARE
  Minsal               NUMBER;
  Maxsal               NUMBER;
  Salary_out_of_range  EXCEPTION;

BEGIN
  /* Retrieve minimum & maximum salary for employee's new job classification
     from SALGRADE table into MINSAL and MAXSAL: */

  SELECT Minsal, Maxsal INTO Minsal, Maxsal
  FROM Salgrade
  WHERE Job_classification = :NEW.Job;

  /* If employee's new salary is less than or greater than
     job classification's limits, raise exception.
     Exception message is returned and pending INSERT or UPDATE statement
     that fired the trigger is rolled back: */

  IF (:NEW.Sal < Minsal OR :NEW.Sal > Maxsal) THEN
    RAISE Salary_out_of_range;
  END IF;
EXCEPTION
  WHEN Salary_out_of_range THEN
    Raise_application_error (
      -20300,
      'Salary '|| TO_CHAR(:NEW.Sal) ||' out of range for '
      || 'job classification ' ||:NEW.Job
      ||' for employee ' || :NEW.Ename
    );
  WHEN NO_DATA_FOUND THEN
    Raise_application_error(-20322, 'Invalid Job Classification');
END;
/

Triggers for Complex Security Authorizations

Triggers are commonly used to enforce complex security authorizations for table data. Only use triggers to enforce complex security authorizations that cannot be defined using the database security features provided with the database. For example, a trigger can prohibit updates to salary data of the emp table during weekends, holidays, and nonworking hours.

When using a trigger to enforce a complex security authorization, it is best to use a BEFORE statement trigger. Using a BEFORE statement trigger has these benefits:

• The security check is done before the triggering statement is allowed to run, so that no wasted work is done by an unauthorized statement.

• The security check is performed only once for the triggering statement, not for each row affected by the triggering statement.

The trigger in Example 9-24 enforces security.

CREATE TABLE Company_holidays (Day DATE);

CREATE OR REPLACE TRIGGER Emp_permit_changes
  BEFORE INSERT OR DELETE OR UPDATE ON Emp
DECLARE
  Dummy              INTEGER;
  Not_on_weekends    EXCEPTION;
  Not_on_holidays    EXCEPTION;
  Non_working_hours  EXCEPTION;
BEGIN
   /* Check for weekends: */
   IF (TO_CHAR(Sysdate, 'DY') = 'SAT' OR
     TO_CHAR(Sysdate, 'DY') = 'SUN') THEN
       RAISE Not_on_weekends;
   END IF;

   /* Check for company holidays: */
   SELECT COUNT(*) INTO Dummy FROM Company_holidays
     WHERE TRUNC(Day) = TRUNC(Sysdate); -- Discard time parts of dates
   IF dummy > 0 THEN
     RAISE Not_on_holidays;
   END IF;

  /* Check for work hours (8am to 6pm): */
  IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
    TO_CHAR(Sysdate, 'HH24') > 18) THEN
      RAISE Non_working_hours;
  END IF;
EXCEPTION
  WHEN Not_on_weekends THEN
    Raise_application_error(-20324,'Might not change '
      ||'employee table during the weekend');
  WHEN Not_on_holidays THEN
    Raise_application_error(-20325,'Might not change '
      ||'employee table during a holiday');
  WHEN Non_working_hours THEN
    Raise_application_error(-20326,'Might not change '
     ||'emp table during nonworking hours');
END;
/

Triggers for Transparent Event Logging

Triggers are very useful when you want to transparently perform a related change in the database following certain events.

The REORDER trigger example shows a trigger that reorders parts as necessary when certain conditions are met. (In other words, a triggering statement is entered, and the PARTS_ON_HAND value is less than the REORDER_POINT value.)

Triggers for Deriving Column Values

Triggers can derive column values automatically, based upon a value provided by an INSERT or UPDATE statement. This type of trigger is useful to force values in specific columns that depend on the values of other columns in the same row. BEFORE row triggers are necessary to complete this type of operation for these reasons:

• The dependent values must be derived before the INSERT or UPDATE occurs, so that the triggering statement can use the derived values.

• The trigger must fire for each row affected by the triggering INSERT or UPDATE statement.

The trigger in Example 9-25 derives new column values for a table whenever a row is inserted or updated.

ALTER TABLE Emp ADD(
   Uppername   VARCHAR2(20),
   Soundexname VARCHAR2(20));

CREATE OR REPLACE TRIGGER Derived 
BEFORE INSERT OR UPDATE OF Ename ON Emp

/* Before updating the ENAME field, derive the values for
   the UPPERNAME and SOUNDEXNAME fields. Restrict users
   from updating these fields directly: */
FOR EACH ROW
BEGIN
  :NEW.Uppername := UPPER(:NEW.Ename);
  :NEW.Soundexname := SOUNDEX(:NEW.Ename);
END;
/

Triggers for Building Complex Updatable Views

Views are an excellent mechanism to provide logical windows over table data. However, when the view query gets complex, the system implicitly cannot translate the DML on the view into those on the underlying tables. INSTEAD OF triggers help solve this problem. These triggers can be defined over views, and they fire instead of the actual DML.

Consider a library system where books are arranged by title. The library consists of a collection of book type objects:

CREATE OR REPLACE TYPE Book_t AS OBJECT (
  Booknum    NUMBER,
  Title      VARCHAR2(20),
  Author     VARCHAR2(20),
  Available  CHAR(1)
);
/
CREATE OR REPLACE TYPE Book_list_t AS TABLE OF Book_t;
/

The table Book_table is created and populated like this:

DROP TABLE Book_table;
CREATE TABLE Book_table (
  Booknum    NUMBER,
  Section    VARCHAR2(20),
  Title      VARCHAR2(20),
  Author     VARCHAR2(20),
  Available  CHAR(1)
);
 
INSERT INTO Book_table (
  Booknum, Section, Title, Author, Available
) 
VALUES (
  121001, 'Classic', 'Iliad', 'Homer', 'Y'
);
 
INSERT INTO Book_table (
  Booknum, Section, Title, Author, Available
) 
VALUES ( 
  121002, 'Novel', 'Gone with the Wind', 'Mitchell M', 'N'
);
 
SELECT * FROM Book_table;

Result:

BOOKNUM SECTION              TITLE                AUTHOR               A
---------- -------------------- -------------------- -------------------- -
    121001 Classic              Iliad                Homer                Y
    121002 Novel                Gone with the Wind   Mitchell M           N
 
2 rows selected.

The table Library_table is created and populated like this:

DROP TABLE Library_table;
CREATE TABLE Library_table (Section VARCHAR2(20));
 
INSERT INTO Library_table (Section)
VALUES ('Novel');
 
INSERT INTO Library_table (Section)
VALUES ('Classic');
 
SELECT * FROM Library_table;

Result:

SECTION
--------------------
Novel
Classic
 
2 rows selected.

You can define a complex view over the tables Book_table and Library_table to create a logical view of the library with sections and a collection of books in each section:

CREATE OR REPLACE VIEW Library_view AS
  SELECT i.Section, CAST (
    MULTISET (
      SELECT b.Booknum, b.Title, b.Author, b.Available
      FROM Book_table b
      WHERE b.Section = i.Section
    ) AS Book_list_t
  ) BOOKLIST
  FROM Library_table i;

(For information about the CAST function, see Oracle Database SQL Language Reference.)

Make Library_view updatable by defining an INSTEAD OF trigger on it:

CREATE OR REPLACE TRIGGER Library_trigger
  INSTEAD OF
  INSERT ON Library_view
  FOR EACH ROW
DECLARE
  Bookvar  Book_t;
  i        INTEGER;
BEGIN
  INSERT INTO Library_table
  VALUES (:NEW.Section);
 
  FOR i IN 1..:NEW.Booklist.COUNT LOOP
    Bookvar := :NEW.Booklist(i);
 
    INSERT INTO Book_table (
      Booknum, Section, Title, Author, Available      
    )
    VALUES (
      Bookvar.booknum, :NEW.Section, Bookvar.Title,
      Bookvar.Author, bookvar.Available
    );
  END LOOP;
END;
/

Insert a new row into Library_view:

INSERT INTO Library_view (Section, Booklist)
VALUES (
  'History', 
  book_list_t (book_t (121330, 'Alexander', 'Mirth', 'Y'))
);

See the effect on Library_view:

SELECT * FROM Library_view;

Result:

SECTION
--------------------
BOOKLIST(BOOKNUM, TITLE, AUTHOR, AVAILABLE)
--------------------------------------------------------------------
 
Novel
BOOK_LIST_T(BOOK_T(121002, 'Gone with the Wind', 'Mitchell M', 'N'))
 
Classic
BOOK_LIST_T(BOOK_T(121001, 'Iliad', 'Homer', 'Y'))
 
History
BOOK_LIST_T(BOOK_T(121330, 'Alexander', 'Mirth', 'Y'))
 
 
3 rows selected.

See the effect on Book_table:

SELECT * FROM Book_table;

Result:

BOOKNUM SECTION              TITLE                AUTHOR               A
---------- -------------------- -------------------- -------------------- -
    121001 Classic              Iliad                Homer                Y
    121002 Novel                Gone with the Wind   Mitchell M           N
    121330 History              Alexander            Mirth                Y
 
3 rows selected.

See the effect on Library_table:

SELECT * FROM Library_table;

Result:

SECTION
--------------------
Novel
Classic
History
 
3 rows selected.

Similarly, you can also define triggers on the nested table booklist to handle modification of the nested table element.

Triggers for Fine-Grained Access Control

You can use LOGON triggers to run the package associated with an application context. An application context captures session-related information about the user who is logging in to the database. From there, your application can control how much access this user has, based on his or her session information.

How Triggers Publish Events

When the database detects an event, the trigger mechanism runs the action specified in the trigger. The action can include publishing the event to a queue so that subscribers receive notifications. To publish events, use the DBMS_AQ package.

When it detects an event, the database fires all triggers that are enabled on that event, except:

• Any trigger that is the target of the triggering event.

  For example, a trigger for all DROP events does not fire when it is dropped itself.

• Any trigger that was modified, but not committed, in the same transaction as the triggering event.

  For example, recursive DDL in a system trigger might modify a trigger, which prevents the modified trigger from being fired by events in the same transaction.

Publication Context

When an event is published, certain run-time context and attributes, as specified in the parameter list, are passed to the callout subprogram. A set of functions called event attribute functions are provided.

For each supported database event, you can identify and predefine event-specific attributes for the event. You can choose the parameter list to be any of these attributes, along with other simple expressions. For callouts, these are passed as IN arguments.

Error Handling

Return status from publication callout functions for all events are ignored. For example, with SHUTDOWN events, the database cannot do anything with the return status.

Execution Model

Traditionally, triggers run as the definer of the trigger. The trigger action of an event runs as the definer of the action (as the definer of the package or function in callouts, or as owner of the trigger in queues). Because the owner of the trigger must have EXECUTE privileges on the underlying queues, packages, or subprograms, this action is consistent.

Event Attribute Functions

When the database fires a trigger, you can retrieve certain attributes about the event that fired the trigger. You can retrieve each attribute with a function call. Table 9-2 describes the system-defined event attributes.

ora_client_ip_address

VARCHAR2

DECLARE
  v_addr VARCHAR2(11);
BEGIN
  IF (ora_sysevent = 'LOGON') THEN
    v_addr := ora_client_ip_address;
  END IF;
END;
/

ora_database_name

VARCHAR2(50)

DECLARE
  v_db_name VARCHAR2(50);
BEGIN
  v_db_name := ora_database_name;
END;
/

ora_des_encrypted_password

VARCHAR2

IF (ora_dict_obj_type = 'USER') THEN  
  INSERT INTO event_table
    VALUES (ora_des_encrypted_password);
END IF;

ora_dict_obj_name

VARCHAR(30)

INSERT INTO event_table 
  VALUES ('Changed object is ' || 
           ora_dict_obj_name);

ora_dict_obj_name_list
(name_list OUT ora_name_list_t)

PLS_INTEGER

DECLARE
name_list   DBMS_STANDARD.ora_name_list_t;
number_modified  PLS_INTEGER;
BEGIN
IF (ora_sysevent='ASSOCIATE STATISTICS')
  THEN number_modified :=
       ora_dict_obj_name_list(name_list);
END IF;
END;

ora_dict_obj_owner

VARCHAR(30)

INSERT INTO event_table 
  VALUES ('object owner is' || 
           ora_dict_obj_owner);

ora_dict_obj_owner_list
(owner_list OUT ora_name_list_t)

PLS_INTEGER

DECLARE
owner_list
   DBMS_STANDARD.ora_name_list_t;
number_modified   PLS_INTEGER;
BEGIN
IF (ora_sysevent='ASSOCIATE STATISTICS')
  THEN number_modified :=
       ora_dict_obj_name_list(owner_list);
END IF;
END;

ora_dict_obj_type

VARCHAR(20)

INSERT INTO event_table 
  VALUES ('This object is a ' || 
           ora_dict_obj_type);

ora_grantee
(user_list OUT ora_name_list_t)

PLS_INTEGER

DECLARE
user_list   DBMS_STANDARD.ora_name_list_t;
number_of_grantees   PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT') THEN
number_of_grantees := 
 ora_grantee(user_list);
END IF;
END; 

ora_instance_num

NUMBER

IF (ora_instance_num = 1) THEN
  INSERT INTO event_table VALUES ('1');
END IF;

ora_is_alter_column
(column_name IN VARCHAR2)

BOOLEAN

IF (ora_sysevent = 'ALTER' AND
    ora_dict_obj_type = 'TABLE') THEN 
 alter_column := ora_is_alter_column('C');
END IF;

ora_is_creating_nested_table

BOOLEAN

IF (ora_sysevent = 'CREATE' and
    ora_dict_obj_type = 'TABLE' and
    ora_is_creating_nested_table) THEN 
  INSERT INTO event_table
    VALUES ('A nested table is created');
END IF;

ora_is_drop_column
(column_name IN VARCHAR2)

BOOLEAN

IF (ora_sysevent = 'ALTER' AND
    ora_dict_obj_type = 'TABLE') THEN  
 drop_column := ora_is_drop_column('C');
END IF;

ora_is_servererror

BOOLEAN

IF ora_is_servererror(error_number) THEN
 INSERT INTO event_table
   VALUES ('Server error!!');
END IF;

ora_login_user

VARCHAR2(30)

SELECT ora_login_user 
FROM DUAL;

ora_partition_pos

PLS_INTEGER

-- Retrieve ora_sql_txt into
-- sql_text variable first.
v_n := ora_partition_pos;
v_new_stmt := SUBSTR(sql_text,1,v_n - 1)
         || ' ' || my_partition_clause
         || ' ' || SUBSTR(sql_text, v_n));

ora_privilege_list
(privilege_list
 OUT ora_name_list_t)

PLS_INTEGER

DECLARE
privelege_list
        DBMS_STANDARD.ora_name_list_t;
number_of_privileges   PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT' OR
    ora_sysevent = 'REVOKE') THEN
  number_of_privileges :=
    ora_privilege_list(privilege_list);
END IF;
END;

ora_revokee
(user_list OUT ora_name_list_t)

PLS_INTEGER

DECLARE
user_list   DBMS_STANDARD.ora_name_list_t;
number_of_users   PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'REVOKE') THEN
number_of_users := ora_revokee(user_list);
END IF;
END;

ora_server_error

NUMBER

INSERT INTO event_table 
  VALUES ('top stack error ' || 
           ora_server_error(1));

ora_server_error_depth

PLS_INTEGER

n := ora_server_error_depth;
-- This value is used with other functions -- such as ora_server_error

ora_server_error_msg
(position in pls_integer)

VARCHAR2

INSERT INTO event_table
  VALUES ('top stack error message' ||
           ora_server_error_msg(1));

ora_server_error_num_params
(position in pls_integer)

PLS_INTEGER

n := ora_server_error_num_params(1);

ora_server_error_param
(position in pls_integer,
 param in pls_integer)

VARCHAR2

-- For example, the second %s in a 
-- message: "Expected %s, found %s"
param := ora_server_error_param(1,2);

ora_sql_txt
(sql_text out ora_name_list_t)

PLS_INTEGER

--...
-- Create table event_table
create table event_table (col
 VARCHAR2(2030));
--...
DECLARE
sql_text   DBMS_STANDARD.ora_name_list_t;
n   PLS_INTEGER;
v_stmt VARCHAR2(2000);
BEGIN
n := ora_sql_txt(sql_text);
 
FOR i IN 1..n LOOP
v_stmt := v_stmt || sql_text(i);
END LOOP;
 
INSERT INTO event_table VALUES ('text of
 triggering statement: ' || v_stmt);
END;

ora_sysevent

VARCHAR2(20)

INSERT INTO event_table
  VALUES (ora_sysevent);

ora_with_grant_option

BOOLEAN

IF (ora_sysevent = 'GRANT' and
    ora_with_grant_option = TRUE) THEN
  INSERT INTO event_table 
    VALUES ('with grant option');
END IF;

space_error_info
(error_number OUT NUMBER,
 error_type OUT VARCHAR2,
 object_owner OUT VARCHAR2,
 table_space_name OUT 
  VARCHAR2,
 object_name OUT VARCHAR2,
 sub_object_name OUT VARCHAR2)

BOOLEAN

IF (space_error_info(eno,typ,owner,ts,obj, 
                     subobj) = TRUE) THEN
  DBMS_OUTPUT.PUT_LINE('The object '|| obj 
               || ' owned by ' || owner ||
               ' has run out of space.');
END IF;

Database Events

Database events are related to entire instances or schemas, not individual tables or rows. Triggers associated with startup and shutdown events must be defined on the database instance. Triggers associated with on-error and suspend events can be defined on either the database instance or a particular schema.

STARTUP

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name

SHUTDOWN

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name

DB_ROLE_CHANGE

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name

SERVERERROR

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
space_error_info

Client Events

Client events are the events related to user logon/logoff, DML, and DDL operations.

The LOGON and LOGOFF events allow simple conditions on UID and USER. All other events allow simple conditions on the type and name of the object, and functions like UID and USER.

The LOGON event starts a separate transaction and commits it after firing the triggers. All other events fire the triggers in the existing user transaction.

The LOGON and LOGOFF events can operate on any objects. For all other events, the corresponding trigger cannot perform any DDL operations, such as DROP and ALTER, on the object that caused the event to be generated.

The DDL allowed inside these triggers is altering, creating, or dropping a table, creating a trigger, and compile operations.

If an event trigger becomes the target of a DDL operation (such as CREATE TRIGGER), it cannot fire later during the same transaction

BEFORE ALTER

AFTER ALTER

ora_sysevent 
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type 
ora_dict_obj_name
ora_dict_obj_owner
ora_des_encrypted_password
 (for ALTER USER events)
ora_is_alter_column
 (for ALTER TABLE events)
ora_is_drop_column
 (for ALTER TABLE events)

BEFORE DROP

AFTER DROP

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner

BEFORE ANALYZE

AFTER ANALYZE

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner

BEFORE ASSOCIATE STATISTICS

AFTER ASSOCIATE STATISTICS

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_dict_obj_name_list
ora_dict_obj_owner_list

BEFORE AUDIT
AFTER AUDIT

BEFORE NOAUDIT
AFTER NOAUDIT

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name

BEFORE COMMENT

AFTER COMMENT

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner

BEFORE CREATE 

AFTER CREATE

ora_sysevent 
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type 
ora_dict_obj_name
ora_dict_obj_owner
ora_is_creating_nested_table
 (for CREATE TABLE events)

BEFORE DDL

AFTER DDL

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner

BEFORE DISASSOCIATE STATISTICS

AFTER DISASSOCIATE STATISTICS

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_dict_obj_name_list
ora_dict_obj_owner_list

BEFORE GRANT

AFTER GRANT

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_grantee
ora_with_grant_option
ora_privileges

BEFORE LOGOFF

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name

AFTER LOGON

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_client_ip_address

BEFORE RENAME

AFTER RENAME

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_owner
ora_dict_obj_type

BEFORE REVOKE

AFTER REVOKE

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_revokee
ora_privileges

AFTER SUSPEND

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
space_error_info

BEFORE TRUNCATE

AFTER TRUNCATE

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner