Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 38 ยท Packages and Triggers

INSTEAD OF, Compound and System Triggers in Oracle

Make views updatable with INSTEAD OF triggers, avoid the mutating table error with compound triggers, and react to DDL and logon events with system triggers.

DML triggers cover the everyday cases. Three more kinds solve problems the basic ones cannot: INSTEAD OF triggers let you change data through views that Oracle would otherwise treat as read-only, compound triggers gather the work of several timing points in one place and cure the mutating table error, and system triggers react to database events like logons and DDL.

Oracle cannot run on this site. Oracle trigger code is read-only. The runnable examples use SQLite, which also supports INSTEAD OF triggers on views, and a Python model of the compound-trigger pattern.

INSTEAD OF triggers on views

A view built from a join, an aggregate or DISTINCT is usually not updatable: Oracle cannot tell which base-table row an UPDATE on the view should change. An INSTEAD OF trigger replaces the DML with code you write.

SQL
CREATE OR REPLACE VIEW employee_departments AS
  SELECT e.employee_id, e.first_name, e.salary, d.department_name
    FROM employees e
    JOIN departments d ON d.department_id = e.department_id;

CREATE OR REPLACE TRIGGER employee_departments_upd
  INSTEAD OF UPDATE ON employee_departments
  FOR EACH ROW
BEGIN
  UPDATE employees
     SET first_name = :NEW.first_name,
         salary     = :NEW.salary
   WHERE employee_id = :OLD.employee_id;
END;
/

Now UPDATE employee_departments SET salary = 6000 WHERE employee_id = 101; works: the trigger fires for each row and does the real update on the base table. INSTEAD OF triggers exist only on views, are always row-level, and need no BEFORE or AFTER. Similarly, you can write INSTEAD OF INSERT to split one insert into several tables, or INSTEAD OF DELETE to turn a delete into a "soft delete" flag.

SQL INSTEAD OF trigger on a join view (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Compound triggers

A compound trigger (Oracle 11g and later) puts all four timing points for one table in a single trigger, with shared variables between them:

SQL
CREATE OR REPLACE TRIGGER employees_ct
  FOR UPDATE OF salary ON employees
  COMPOUND TRIGGER

  TYPE t_ids IS TABLE OF employees.employee_id%TYPE;
  g_changed t_ids := t_ids();            -- shared across the sections

  BEFORE STATEMENT IS
  BEGIN
    g_changed.DELETE;                    -- start clean for this statement
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    g_changed.EXTEND;
    g_changed(g_changed.LAST) := :NEW.employee_id;    -- only collect keys
  END AFTER EACH ROW;

  AFTER STATEMENT IS
    v_max NUMBER;
  BEGIN
    -- the table is no longer mutating here, so querying it is allowed
    SELECT MAX(salary) INTO v_max FROM employees;
    DBMS_OUTPUT.PUT_LINE(g_changed.COUNT || ' rows changed, top salary now ' || v_max);
  END AFTER STATEMENT;

END employees_ct;
/

The four possible sections are BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW and AFTER STATEMENT. Variables declared before them live for one triggering statement and are discarded afterwards, so nothing leaks between statements. The pattern is: collect keys or values row by row, act once at the end. That also fixes ORA-04091, because the AFTER STATEMENT section may query the table, and reduces overhead since you run one query rather than one per row.

The Python model below is a stand-in that shows the shape of the pattern: row events fill a list and the statement-end hook uses it once.

Python Collect per row, act once (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

System triggers

System (event) triggers fire on database or schema events rather than table changes.

Level Events
ON DATABASE / ON SCHEMA LOGON, LOGOFF, STARTUP, SHUTDOWN, SERVERERROR
DDL BEFORE/AFTER CREATE, ALTER, DROP, TRUNCATE, DDL
SQL
CREATE OR REPLACE TRIGGER ddl_audit_trg
  AFTER DDL ON SCHEMA
BEGIN
  INSERT INTO ddl_log (event, object_type, object_name, done_by, done_at)
  VALUES (ORA_SYSEVENT, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, USER, SYSDATE);
END;
/

CREATE OR REPLACE TRIGGER logon_audit_trg
  AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO logon_log VALUES (USER, SYSTIMESTAMP, SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;
/

The ORA_* functions (such as ORA_SYSEVENT) describe the event. Creating a database-level trigger needs the ADMINISTER DATABASE TRIGGER privilege. Be careful with LOGON triggers: an error in one can lock out ordinary users (administrators with ADMINISTER DATABASE TRIGGER can still connect and fix it). Keep them fast and wrap risky code in an exception handler.

Trigger order and the FOLLOWS clause

Several triggers on the same event and level can exist. Oracle 11g lets you control their order with FOLLOWS other_trigger; before that the order was undefined. If one trigger's logic depends on another's, state it explicitly or merge them into a compound trigger.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. On which kind of object can an INSTEAD OF trigger be created?

    INSTEAD OF triggers replace DML on views, which are otherwise not updatable when they hide joins or aggregates.
  2. 2. Why does a compound trigger help with the mutating table error?

    Collect keys in row sections, then query the table once in AFTER STATEMENT.
  3. 3. How long do variables declared in a compound trigger's declaration section live?

    They are created when the statement starts and discarded when it ends.
  4. 4. Which privilege lets a user create AFTER LOGON ON DATABASE triggers?

    Database-level triggers need ADMINISTER DATABASE TRIGGER.

Summary

  • INSTEAD OF triggers make complex views writable by redirecting DML to the base tables.
  • A compound trigger combines BEFORE/AFTER STATEMENT and BEFORE/AFTER EACH ROW sections with shared per-statement variables, the standard cure for ORA-04091.
  • System triggers fire on logon, startup, DDL and errors; keep them short and safe.
  • Use FOLLOWS to order triggers, or merge them.

Next: Exception Handling Basics